SQL join returning all not

This is for Oracle.
I need to select the drawings that are NOT in the packlist table.
In other words, drawings that have no packlists.
This returns 91 rows, but there should only be about 3.
It returns all the drawing numbers multiple times.
I don't know how to do an inner join in oracle.
Can you help? Thanks! Beth
SELECT DRAWING.DRAWING, CUSTOMER.CUSTOMER_NAME, DRAWING.CUSTOMER_PART_NO
FROM CUSTOMER, DRAWING, PACKLIST
WHERE  DRAWING.DRAWING <>PACKLIST.DRAWING
AND CUSTOMER.CUSTOMER_NO = DRAWING.CUSTOMER_NO;

Open in new window

ba_trainerAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
pssandhuConnect With a Mentor Commented:
Something like:
SELECT  DRAWING.DRAWING, CUSTOMER.CUSTOMER_NAME, DRAWING.CUSTOMER_PART_NO
FROM    CUSTOMER, DRAWING
WHERE   CUSTOMER.CUSTOMER_NO = DRAWING.CUSTOMER_NO
        AND DRAWING.DRAWING NOT IN (SELECT Drawing From PackList)

Open in new window

0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Hope this helps:
SELECT DRAWING, CUSTOMER.CUSTOME, DRAWING.CUSTOME
FROM CUSTOMER 
INNER JOIN DRAWING ON CUSTOMER.CUSTOMER_NO = DRAWING.CUSTOMER_NO
LEFT OUTER JOIN PACKLIST ON DRAWING.DRAWING = PACKLIST.DRAWING
WHERE PACKLIST.DRAWING IS NULL

Open in new window

0
 
mrjoltcolaCommented:
Inner join plus a check for non-existance would be something like:

SELECT DRAWING.DRAWING, CUSTOMER.CUSTOMER_NAME, DRAWING.CUSTOMER_PART_NO
FROM CUSTOMER INNER JOIN DRAWING ON DRAWING.CUSTOMER_NO = CUSTOMER.CUSTOMER_NO
WHERE NOT EXISTS (SELECT 1 FROM PACKLIST WHERE DRAWING.DRAWING = PACKLIST.DRAWING)
;
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.