[Help] SQL Query

[[LAPD]Cheetah]

I want to make a sql query, stored procedure, or whatever which will:

Say there are two tables [Computers] and [Hard Drives]

Computers (Computer_ID, Host_Name, Etc)

Hard Drives(Hard_Drive_ID, Size, Computer_ID<--foreign key)

I want to select all computer_id's from computers where there does not exist a computer_id in the hard drives table. That is, show me all computers who do not have hard drives.

Anyone know?

 

[Calder]

Use NOT IN with a subquery selecting hard drives?

 

[Brasstax]

look at the NOT IN statement

edit - fuck you caldersores

 

[Diablo Escobar]

^^^

so select t1.computer_id from t1.computers where computer id not in (select t2. computer_id from t2.hard_drives) or something to that effect

 

[Wrathchild]

 

[Trinket]

Something like...

SELECT c.computer_id
FROM Computers AS c
WHERE c.computer_id NOT IN
(SELECT c2.computer_id
FROM Computer c2 INNER JOIN HardDrive AS h
ON c2.computer_id=h.computer_id);

??

 

[Fox]

Weird, I didn't know you can run a query within another query.

 

[Trinket]

if that was sarcasim, you're retarded, because you can.

If you were being honest, then yeah, it makes things alot easier.

 

[Fox]

if that was sarcasim, you're retarded, because you can.

If you were being honest, then yeah, it makes things alot easier.

I really didn't know. This would've helped yesterday when I got stumped for an hour trying to use some clever JOIN instead.

 

[Trinket]

heh, yeah, selects within selects ftw

 

[[Ms] iNsAnE]

If what they said doesnt work, maybe save the results from the Hard_Drive_ID query first (To a variable). Then query the Computer_IDs using that result.

 

[Calder]

Something like...

SELECT c.computer_id
FROM Computers AS c
WHERE c.computer_id NOT IN
(SELECT c2.computer_id
FROM Computer c2 INNER JOIN HardDrive AS h
ON c2.computer_id=h.computer_id);

??

You can just select all of hard drive and leave out the join ^_^

 

[Trinket]

you could do that too

 

[[LAPD]Cheetah]

woot that worked thanks guys.

The computer/hard drive relationship was fictitious but this would have been the code which worked.
SELECT Computer_ID
FROM Computer
WHERE (Computer_ID NOT IN
(SELECT Computer_ID
FROM Hard_Drives))

This is an awesome operator! Thanks all