CODEWITHSUNDEEP
sql
Fake Name
Fake Name

Reputation: 1

Selecting rows where all values not in another table

This is my table design:

REQUEST(R_ID,attr1,attr2)
WIPS(R_ID,WIP,attr3,attr4)
SHIPPING(WIP,attr5,attr6)

How do I limit my query to show the information from REQUEST but only where the all of the wips associated between REQUEST and WIPS are not in the SHIPPING table. For example, the SHIPPING table has all of the WIPS that have been shipped, I only want to show the REQUEST rows where all of the WIPS have not shipped. Any help would be greatly appreciated.

Thanks, -Steve

Upvotes: 0

Views: 262

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 658897

Since you are looking for rows where wips associated between REQUEST and WIPS it is not enough to rule out a connection to shipping. We need to establish a connection to wip to start with:

SELECT *
FROM   request r
WHERE  EXISTS (
   SELECT 1
   FROM   wips w1
   LEFT   JOIN shipping s USING (wip)
   WHERE  w1.r_id = r.r_id
   AND    s.wip IS NULL
  )

If your RDBMS does not have the USING clause for JOIN, use instead:

   LEFT   JOIN shipping s ON s.wip = w1.wip

If you actually mean, that none of the wips associated with a request can be connected to a shipping:

SELECT *
FROM   request r
WHERE NOT EXISTS (
   SELECT 1
   FROM   wips w1
   JOIN   shipping s USING (wip)
   WHERE  w1.r_id = r.r_id
  )
-- but has to have at least one wip connected?
AND    EXISTS (
   SELECT 1
   FROM   wips w1
   WHERE  w1.r_id = r.r_id
  )

-> SQLfiddle demonstrating important details.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270993

You can do this with not exists:

select *
from request r
where not exists (select 1
                  from wips w join
                       shipping s
                       on w.wip = s.wip
                  where w.r_id = r.r_id
                 )

That is, if anything exists in the shipping table for the wips on the request, then filter out the row.

Upvotes: 1

Related Questions