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
Solved

SQL join returning all not

Posted on 2009-07-14
3
217 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

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SYbase 4 36
Using SQL*PLUS issue with where statement with trunc function 3 27
SQL query and VBA 5 45
Where on a calculated field 1 20
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

809 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