RIGHT JOIN

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

RIGHT JOIN

Post 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
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: RIGHT JOIN

Post 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
alex.barylski
DevNet Evangelist
Posts: 6267
Joined: Tue Dec 21, 2004 5:00 pm
Location: Winnipeg

Re: RIGHT JOIN

Post 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
Post Reply