Page 1 of 1

LEFT JOIN, RIGHT JOIN same query?

Posted: Thu Apr 01, 2010 9:29 am
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

Re: LEFT JOIN, RIGHT JOIN same query?

Posted: Thu Apr 01, 2010 9:59 am
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]