CODEWITHSUNDEEP
mysql
Nishant N
Nishant N

Reputation: 82

Using join in place of this two seperate queries

I have a table by the following structure and records, All i want to do is to just extract list_name of those records whose uid is either 0 or 2 , but i also want to check if a record is available for uid 0 and 2 , then only it should show only record with uid 2... I have managed to do this with two queries... Can i write a single query for this...

**id**    **uid**     **list_name**
1            2        favourite list
2            0        Things i love
3            0        my list 
4            2        my lists
5            3        test334
6            2        Things i love

Any help or suggestion will be highly appreciated ..Thanks in advance..

Upvotes: 0

Views: 60

Answers (2)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115510

Another guess:

SELECT
    uid, list_name
FROM
    myTable T1
WHERE
   uid = 2
   OR
   ( uid = 0 
     AND NOT EXISTS
             ( SELECT * 
               FROM myTable T2 
               WHERE T2.uid = 2
                 AND T2.list_name = T1.list_name
             )
   )

Upvotes: 1

gbn
gbn

Reputation: 432180

A guess...

SELECT
    list_name
FROM
    myTable T1
WHERE
   uid IN (0, 2)
   AND
   NOT EXISTS (SELECT * FROM myTable T2 WHERE T2.uid = 0)
UNION ALL
SELECT
   list_name
FROM
   myTable T1
WHERE
   uid = 2
   AND
   EXISTS (SELECT * FROM myTable T2 WHERE T2.uid = 0)

Upvotes: 1

Related Questions