CODEWITHSUNDEEP
sqlself-join
Charles
Charles

Reputation: 67

Using an outerjoin to find where all corresponding values for a tuple are zero

I have the following table data (e0 is the primary key):

+-----+----+----+----+----+
| e0  | e1 | e2 | e3 | e4 |
+-----+----+----+----+----+
| 111 |  2 |  5 |  7 |  0 |
| 222 |  2 |  5 |  7 |  0 |
| 333 |  3 |  6 |  8 |  7 |
| 444 |  1 |  3 |  2 |  2 |
| 555 |  1 |  3 |  2 |  0 |
| 666 |  1 |  3 |  2 |  0 |
| 777 |  6 |  3 |  4 |  0 |
| 888 |  6 |  3 |  4 |  0 |
| 999 |  6 |  3 |  4 |  0 |
+-----+----+----+----+----+

This is part of an exercise where I need to use an outerjoin to find which tuples of (e1,e2,e3) have ALL corresponding values of e4 as 0 (i.e. the query has to return (2,5,7) and (6,3,4)). I've tried a few solutions, but all of them still include (1,3,2) which is not meant to happen.

Does anybody have an idea for an outerjoin that would return (2,5,7) and (6,3,4)?

Upvotes: 1

Views: 46

Answers (2)

Martin Smith
Martin Smith

Reputation: 453707

I would just use NOT EXISTS but to express that using outer joins you can use the below. 

SELECT DISTINCT a.e1, 
                a.e2, 
                a.e3 
FROM   data a 
       LEFT OUTER JOIN data b 
                    ON a.e1 = b.e1 
                       AND a.e2 = b.e2 
                       AND a.e3 = b.e3 
                       AND b.e4 <> 0 
WHERE  b.e1 IS NULL

SQL Fiddle

And the NOT EXISTS method

SELECT DISTINCT a.e1, 
                a.e2, 
                a.e3 
FROM   data a 
WHERE  NOT EXISTS (SELECT * 
                   FROM   data b 
                   WHERE  a.e1 = b.e1 
                          AND a.e2 = b.e2 
                          AND a.e3 = b.e3 
                          AND b.e4 <> 0)

SQL Fiddle

Upvotes: 1

Jim Dennis
Jim Dennis

Reputation: 17510

I'm not sure if this actually gives the desired results (semantically); but it doesn't use an OUTER JOIN at all:

SELECT e1, e2, e3 FROM (
SELECT e0, e1, e2, e3, e4, COUNT(*) AS c FROM data
    GROUP BY e1, e2, e3
    HAVING c > 1
) AS b
WHERE b.e4 = 0

It does give the rows and columns you specify from your data set; but I'm not sure I'm understanding the question quite right.

Why do you need to use an OUTER JOIN? Is this equivalent to Martin Smith's answer?

Upvotes: 0

Related Questions