Query: Please help

losartan · November 4th, 2004, 11:54 PM

I have four tables that are linked by medication name. One table is the medication inventory of a machine and another table is medications that were loaded into the same machine.

I want to create a query that lists all of the medications that were loaded into the machine that are not in the current inventory (Meds are often removed).

I want it to take all of the medications in the loads table, compare the meds to the current inventory table, and only show the meds from the load table that do not show up on the inventory table.

How would I do this?

Thanks,
Jason

jemacc · November 6th, 2004, 02:48 AM

Can you list your table structure
try this:

Code:

SELECT DISTINCT Table2.col1
FROM Table1 
INNER JOIN Table2 ON Table1.location = Table2.location WHERE Table2.col1 NOT IN (SELECT DISTINCT col FROM Table1);

Jaime E. Maccou
Applications Analyst