Help with Query 1

[Mighty]

Hi Folks,

I have two tables - Part Master and Product Structure. The PRTNUM_01 field in the Part Master table is the same as the PARPRT_02 field in the product structure table.

I want to run a query that will give me all the values from [Part Master].PRTNUM_01 that are not in [Product Structure].PARPRT_02

Any suggestions?

Mighty

 

[easyit]

SELECT [Part Master].*
FROM [product Structure] RIGHT JOIN [Part Master].PRTNUM_01 ON [Product Structure].PARPRT_02 = [Part Master].PRTNUM_01 
WHERE  [Product Structure].PARPRT_02 Is Null;

You can create this without SQL; create a new query; add both tables; select * from [part master];drag PRTNUM_01 to PRTNUM_02 to create a join.
Doubleclick the join, select the optio all records from [part master]. Select the [Product Structure].PARPRT_02 and set null as criteria...

sorry for any typo's...

EasyIT

"Do you think that’s air you're breathing?

 

[PHV]

all the values from [Part Master].PRTNUM_01 that are not in [Product Structure].PARPRT_02
One way:
SELECT * FROM [Part Master]
WHERE PRTNUM_01 Not In (SELECT PARPRT_02 FROM [Product Structure])

Another way:
SELECT M.*
FROM [Part Master] M LEFT JOIN [Product Structure] S ON M.PRTNUM_01 = S.PARPRT_02
WHERE S.PARPRT_02 Is Null

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