Another SQL Query Thread

Slaanesh

Ok here we go,

This one has been bugging me for a while and could be resolved if sub-queries worked in mySQL 4.0. But they don't so I want to figure out how to do it without using sub-queries.

I have tables called 'audio_database' and 'selection_details'

In 'selection_details' there is 2 fields, 'selection_id' and 'track_id'.
The 'track_id' field corresponds to a 'track_id' field in the 'audio_database' table.
I want to write a query that returns all track_id's that exist in 'audio_database' but don't exist in 'selection_details' where 'selection_id = 57'.

Note: Their are duplicate track_id's in 'selection_details'.

Solution is probably simple, just been wrecking my head for a while now.
I thought I had the write query but it took so long that root killed the thread !

Thanks.

zoro

Slaanesh wrote:

I have tables called 'audio_database' and 'selection_details'
In 'selection_details' there is 2 fields, 'selection_id' and 'track_id'.

The 'track_id' field corresponds to a 'track_id' field in the 'audio_database' table.

I want to write a query that returns all track_id's that exist in 'audio_database' but don't exist in 'selection_details' where 'selection_id = 57'.
Note: Their are duplicate track_id's in 'selection_details'.

hmmm...
SELECT AD.track_id, AD.track_name FROM audio_database AD, selection_details SD
WHERE SD.selection_details = 57 AND AD.track_id NOT IN (SELECT track_id FROM selection_details)

???

*edit* I just used what you told me not to use didnt I?

JackieChan

Slaanesh,
Solution is succint.

I'll give you the solution in Oracle version of SQL. You can tweak it for mySQL if required

select track_id from audio_database where track_id not in
(select track_id from selection_details where selection_id=57);

JC

Slaanesh

zoro wrote:

hmmm...
SELECT AD.track_id, AD.track_name FROM audio_database AD, selection_details SD
WHERE SD.selection_details = 57 AND AD.track_id NOT IN (SELECT track_id FROM selection_details)

???

*edit* I just used what you told me not to use didnt I?

Hehe, no sub-queries !

zoro

Slaanesh wrote:

Hehe, no sub-queries !

shut up

Slaanesh

No solutions at all lads ? 'NOT IN()' is a subquery !
Well, it doesn't work in mySQL 4.0 is all I know.

YellowMan

C'mon !
Even if mySQL is not a database (just joking), there is a solution

select ad.track_id
from audio_database ad
left join selection_details sd
on ad.track_id=sd.track_id and sd.selection_id=57
where
sd.track_id is null

It will return you all track_ids from audio_database table except those who are in the selection_details table along with id=57.
Not sure about mySQL syntax but it should work with any SQL-ANSI 92 compatible database

Slaanesh

Merci Beacoup Monsieur !

Very good, just what I needed.
Thanks YellowMan, forgot about using LEFT JOIN, would have forgotten about the null part though.

Thanks again,

Slaan.

bonkey

YellowMan wrote:

C'mon !
Not sure about mySQL syntax but it should work with any SQL-ANSI 92 compatible database

Yeah, but sub-querys should also be supported as part of SQL 92, I think

jc

YellowMan

bonkey wrote:

Yeah, but sub-querys should also be supported as part of SQL 92, I think
jc

Well, don't blame mySQL guys much - they do the great job for free, I mean that they don't earn money directly
BTW, SQL ANSI 92 is not a free thing - you have to pay to get it.