Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL join returning all not

Posted on 2009-07-14
3
Medium Priority
?
235 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 17

Accepted Solution

by:
pssandhu earned 2000 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

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

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

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Suggested Courses

705 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