SQL Query-Why doesn't this work? 1

[meegsley]

Hi there,

I have a computer inventory database that tracks donations and distributions. I want to run a query on the combo box, drop down menu for the distributions table that weeds out computers that have already been distributed. My response was to use this SQL statement:

SELECT
Computers.Serial_Number
FROM
inventory.Computers
WHERE
Computers.Serial_Number <> Computer_Dist_Join.Serial_Number

However, it only elimated one serial number. What am I doing wrong? If you need further info let me know. Thanks, I really appreciate your help!

 

[woyler]

You have 3 tables that you are working with?
Computers,Inventory,Computer_Dist_Join?
Try:
SELECT
Computers.Serial_Number
FROM
Computers,Computer_Dist_Join
WHERE
Computers.Serial_Number <> Computer_Dist_Join.Serial_Number

 

[meegsley]

sorry, inventory doesn't need to be there (it's the name of the database). thanks! i'll try it your way.

 

[meegsley]

when i use this code, it spits out duplicates, triplicates in some cases. any other ideas? thanks!

 

[tlbroadbent]

Try this.

SELECT Computers.Serial_Number
FROM Computers
WHERE Computers.Serial_Number Not In (Select Computer_Dist_Join.Serial_Number From Computer_Dist_Join)

Or this.

SELECT Computers.Serial_Number
FROM Computers Left Join Computer_Dist_Join
ON Computers.Serial_Number=Computer_Dist_Join.Serial_Number
WHERE Computer_Dist_Join.Serial_Number Is Null Terry Broadbent
Please review faq183-874.

&quot;The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge.&quot; - Daniel J Boorstin

 

[meegsley]

THANKS Terry,

The first one worked like a charm!! I really appreciate your help.

Meghan