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
RIGHT JOIN
Moderator: General Moderators
-
alex.barylski
- DevNet Evangelist
- Posts: 6267
- Joined: Tue Dec 21, 2004 5:00 pm
- Location: Winnipeg
Re: RIGHT JOIN
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
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
Thanks
Cheers,
Alex