LEFT JOIN, RIGHT JOIN same query?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
alex.barylski
DevNet Evangelist
Posts: 6267
Joined: Tue Dec 21, 2004 5:00 pm
Location: Winnipeg

LEFT JOIN, RIGHT JOIN same query?

Post by alex.barylski »

I have three tables which I need query:

Code: Select all

procedures:
id_primary, id_other

sequences:
id_primary, id_other2

procedure_sequences
id_primary, id_procedure, id_sequence
The last table is a junction table, used to associate X number of sequences with any given procedure. Simple enough, what I am having trouble with, is the requirement to now determine(and fetch) all the procedures associated with a single sequence, that is, I am given the id_primary of a single record in sequences.

For the life of me I cannot wrap my head around how this would be solved using JOIN's?

I am sure the query is relatively simple but I cannot figure it out.

EDIT | Would I have to use a sub-query to solve this issue? By first querying the sequences and junction table to find all the procedure ID's and then SELECT all the procedures whose ID's were previously returned???

Cheers,
Alex
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: LEFT JOIN, RIGHT JOIN same query?

Post by mikosiko »

try this:

[text]
SELECT a.id_other2, b.id_procedure, c.id_other
FROM sequences AS a LEFT JOIN procedure_sequences AS b ON (a.id_primary = b.id_sequence)
LEFT JOIN procedures AS c ON (c.id_primary = b.id_procedure)
WHERE a.id_primary = <your sequence id record>

[/text]
Post Reply