Page 1 of 1

RIGHT JOIN

Posted: Fri Apr 23, 2010 10:19 am
by alex.barylski
I have two tables:

procedure_sequences
sequences

The first is used to associate sequences with records in a 'procedures' table (junction table).

I have the following RIGHT JOIN query

[sql]
SELECT
*
FROM
rpi_procedure_sequences AS rps
RIGHT JOIN
rpi_sequences AS rs
ON
rps.id_sequence = rs.id_primary
[/sql]

This returns all records, including those sequences which are not associate with ant procedures. I want only those sequences which are *not* associated to be returned, how best do I accomplish this requirement?

I thought maybe sub-select to filter out those that exist, but that seems overly complicated???

A WHERE clause testing for NULL's in the sequences fields seems hackish and incomplete???

This query also must eventually be converted into a DELETE query as it's used to remove all sequences that do not have an association in the junction table procedure_sequences.

Cheers,
Alex

Re: RIGHT JOIN

Posted: Fri Apr 23, 2010 10:31 am
by Eran
A where clause testing for nulls is the correct method.

Code: Select all

SELECT rps.* FROM rpi_procedure_sequences AS rps
LEFT JOIN rpi_sequences AS rs ON rps.id_sequence = rs.id_primary
WHERE rs.some_column IS NULL

Re: RIGHT JOIN

Posted: Fri Apr 23, 2010 2:09 pm
by alex.barylski
Yea thats what I ended up doing, I just figured there might be something I was missing about RIGHT JOIN that did this automatically.

Thanks :)

Cheers,
Alex