CODEWITHSUNDEEP
mysqlsql
KingFish
KingFish

Reputation: 9153

mysql selecting a union where values in one don't appear in the other

sorry for the poorly titled post.

Say I have the following table:

C1 | C2       | c3
1  | foo      | x
2  | bar      | y
2  | blaz     | z
3  | something| y
3  | hello    | z
3  | doctor   | x
4  | name     | y
5  | continue | x
5  | yesterday| z
6  | tomorrow | y

I'm trying to come up w/ a sql statement which performs the following union: 1st retrieval retrieves all records w/ c3 = 'y'

2nd retrieval retrieves the first instance of a record where c3 <> 'y' and the result is not in the previous union

So, for the result, I should see:

C1 | C2   
1  | foo  
2  | bar 
3  | something
4  | name
5  | continue
6  | tomorrow

So two questions: 1: Am I totally smoking crack where I think I can do this, and 2: (assuming I can), how do I do this?

Upvotes: 0

Views: 77

Answers (3)

Ambrose
Ambrose

Reputation: 511

Try this one:

SELECT   a.C1, a.C2
  FROM   MyTable a
 WHERE   a.C3 = 'y'
    UNION
SELECT   b.C1, b.C2
  FROM   MyTable b
 WHERE   b.C3 <> 'y' AND
         b.C1 not in 
            (
               SELECT c.C1
                 FROM MyTable c
                WHERE c.C3 = 'y'
            )

UPDATE 1

by the way, why is that there is only one record of 5 in your desired result? where, in fact, there could be two.

SEE FOR DEMO 1

OR

SELECT g.C1, MIN(g.C2) C2
FROM
(SELECT   a.C1, a.C2
  FROM   MyTable a
 WHERE   a.C3 = 'y'
    UNION
SELECT   b.C1, b.C2
  FROM   MyTable b
 WHERE   b.C3 <> 'y' AND
         b.C1 not in 
            (
               SELECT c.C1
                 FROM MyTable c
                WHERE c.C3 = 'y'
            )
) g
GROUP BY g.C1

SEE FOR DEMO 2 (yields same result with your desired result)

Upvotes: 1

Bishnu Paudel
Bishnu Paudel

Reputation: 2079

Try this:

    SELECT C1, C2
    FROM   Table1
    Where  C3 = 'y'

    UNION

   (
    SELECT  C1, C2
    FROM  Table1
    Where C3 <> 'y' ORDER BY C1 LIMIT 1
   )
 ORDER BY C1

Upvotes: 1

Nikola Markovinović
Nikola Markovinović

Reputation: 19346

DEMO @ Sql Fiddle.

select *
  from table1
 where c3 = 'y'
 union all
(select table1.*
  from table1
  left join table1 t1
    on table1.c1 = t1.c1
   and t1.c3 = 'y'
 where table1.c3 <> 'y'
   and t1.c1 is null
 -- The meaning of first becomes clear here
 order by table1.c3, table1.c2
 limit 1)

Note: foo is not in a list because it is marked as x.

Upvotes: 1

Related Questions