List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:October 20 1999 1:27am
Subject:RIGHT JOIN in MySQL??
View as plain text  
>>>>> "Morten" == Morten Winther <mw@stripped> writes:

Morten> Hello!
Morten> I'm new to MySQL and this list.

Morten> I'm looking for a little help with a SQL statement.

Morten> In MS Access my query look like this:

Morten> SELECT DISTINCT projekter.*, personer.*
Morten> FROM (adgang LEFT JOIN ((medlemgrup LEFT JOIN personer ON
Morten> medlemgrup.personID = personer.ID) RIGHT JOIN pgrupper ON
Morten> medlemgrup.gruppeID = pgrupper.ID) ON adgang.gruppeID = pgrupper.ID) RIGHT
Morten> JOIN projekter ON adgang.projektID = projekter.ID
Morten> WHERE personer.ID = 1;

Morten> But from what I understand from the manual it is not posible in MySQL,
Morten> because off the RIGHT JOIN.


Morten> How do I get around this?

Morten> Best regards

Morten> Morten Winther, Denmark

Hi!

Access should never do a RIGHT join against MyODBC, as MyODBC reports
that it can only do LEFT joins.

One of the main reason for Access doing LEFT/RIGHT join, when one
doesn't need to do it, is when one hasn't defined some index as UNIQUE 
or as an primary key.  In many cases Access will then do a very
inefficient query to find the rows.

The question isn't often what Access does;  The question is what you
result you are looking for.  In many cases one can just remove all
LEFT JOIN/RIGHT JOIN definitions from an Access query to get intended
result.

Anyway, assuming you know what you are doing, your query should be
identical to:

SELECT DISTINCT projekter.*, personer.* FROM
projekter LEFT JOIN adgang on adgang.projektID = projekter.ID
LEFT JOIN pgrupper ON adgang.gruppeID = pgrupper
LEFT JOIN medlemgrup ON medlemgrup.gruppeID = pgrupper.ID
LEFT JOIN personer ON medlemgrup.personID = personer.ID
WHERE personer.ID = 1;

(Note that MySQL can't handle joins with are prioritized with braces,
but fortunately this doesn't happen very often with Access)

Regards,
Monty
Thread
RIGHT JOIN in MySQL??Morten Winther19 Oct
  • RIGHT JOIN in MySQL??Michael Widenius20 Oct