Solved

SQL join returning all not

Posted on 2009-07-14
3
200 Views
Last Modified: 2012-05-07
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

0
Comment
Question by:ba_trainer
3 Comments
 
LVL 17

Accepted Solution

by:
pssandhu earned 500 total points
ID: 24852802
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
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24852816
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
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24852825
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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
Delivering innovative fully-managed cloud services for mission-critical applications requires expertise in multiple areas plus vision and commitment. Meet a few of the people behind the quality services of Concerto.

943 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

4 Experts available now in Live!

Get 1:1 Help Now