Not Equal to Question/Problem

[djhankypark]

Hello I am trying to get from a mysql tables, all records from one table that do not have a corresponding ID in another, I am using the following query but I'm missing the plot somewhere.

SELECT tbl_band_songs.id3_artist, tbl_band_songs.Artist, tbl_band_songs.id, tbl_band_songs.id3_title

FROM tbl_band_songs, tbl_song_lyrics

WHERE tbl_band_songs.id3_artist = 239 AND tbl_band_songs.id <> tbl_song_lyrics.Sng_ID

Basically I am trying to show the user songs that have no lyrics already enetered so they then have the option to enter them. Thank you in advance for any help. regards Ray

[J4amieC]

Never touched mysql in my life, but I think you're looking for NOT IN and a subquery.

SELECT tbl_band_songs.id3_artist, tbl_band_songs.Artist, tbl_band_songs.id, tbl_band_songs.id3_title
FROM tbl_band_songs, tbl_song_lyrics
WHERE tbl_band_songs.id3_artist = 239
AND tbl_band_songs.id NOT IN (SELECT tbl_song_lyrics.Sng_ID FROM tbl_song_lyrics)

[David Skelly]

You should do a left outer join from tbl_band_songs to tbl_band_lyrics and then select where tbl_song_lyrics.Sng_ID is null. If you search on Google you will find plenty of examples that explain what an outer join is and how to code it to get the results you want.

[Chris Meech]

My preference for these kinds of queries is to use NOT EXISTS in the where clause.

select your_field_list
from table_one t1
where not exists ( select t2.id
from table_two t2
where t2.id = t1.id
)
;

I've found this to usually be the most performant, though YMMV. :)

Chris Meech I am Canadian. [heard in a local bar] In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]

[PIEBALDconsult]

Yeah, that's what I do.

[J4amieC]

I had an old boss who swore by this route, in favour of my NOT IN solution above. He said it was much more efficient. He was right, and I forgot about that whole conversation till I saw your answer.