JOIN QUESTION, and could use some help. 1

[Kozimoto]

Hello,

I have three tables; tbl_Software, tbl_Tested, tbl_Hardware. When a software package has been tested successfully tbl_Software.SoftwareID and tbl_Hardware.HardwareID are inserted into the same row in the tbl_Tested.

What I'm looking for is the SQL for an Access database to give me only those records from the tbl_Hardware table that have not been inserted into the tbl_Tested table for a specific record from the tbl_Software table. I hope this makes sense.

In otherwords, I need to pass the tbl_Software.SoftwareID to the query as a variable, then let's say I've tested it on 3 out of 5 computers. I need the results to show me the final two computers I haven't tested this particular software package on.

Thanks VERY much if anyone can help me out.

 

[PHV]

One way:
SELECT H.* FROM tbl_Hardware H LEFT JOIN (
SELECT HardwareID FROM tbl_Tested WHERE SoftwareID = [Enter SoftwareID : ]
) T ON H.HardwareID = T.HardwareID
WHERE T.HardwareID Is Null;

Another way:
SELECT H.* FROM tbl_Hardware H
WHERE Not (H.HardwareID IN (SELECT HardwareID FROM tbl_Tested WHERE SoftwareID = [Enter SoftwareID : ]));

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Kozimoto]

That worked perfectly PH. Thanks SO much.