>>>>> "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
