CODEWITHSUNDEEP
sqlunion
Shankar
Shankar

Reputation: 95

How to Intersects two Union queries

Hello I have Two Queries

One is 

SELECT userid_one AS id FROM `friends` WHERE `userid_two` = '$UserId' AND `requeststatus` ='accepted'
                    UNION 
SELECT userid_two AS id FROM `friends` WHERE  `userid_one` =  '$UserId' AND `requeststatus` ='accepted'

Its gives Me userids 41,38,61,62,64

Second Is

SELECT DISTINCT  `userid` FROM  `groupmembers` WHERE  `groupid` =24

It gives me 61,49,62

I want result userid they are not in Both Results I.e:49,41,38,64,38

How can i do that

Upvotes: 3

Views: 68

Answers (5)

Nasir Iqbal
Nasir Iqbal

Reputation: 872

I don't know your database engine. However as JOIN are common among all, I hope following code will work for you

SELECT id FROM (
    SELECT userid_one AS id FROM `friends` 
    WHERE `userid_two` = '$UserId' AND `requeststatus`='accepted'
  UNION 
    SELECT userid_two AS id FROM `friends` 
    WHERE  `userid_one` =  '$UserId' AND `requeststatus`='accepted'
) friends_union FULL OUTER JOIN 
  SELECT DISTINCT `userid` AS id FROM `groupmembers` WHERE `groupid` =24
) group_member ON friends_union.id = group_member.id
WHERE friends_union.id IS NULL OR group_member.id IS NULL

In case you are using MySQL then you have to search a workaround for FULL OUTER JOIN

Upvotes: 0

jophab
jophab

Reputation: 5509

Take UNION ALL between first result (the one with join ) and second result. So that the duplicate values persists. Now use GROUP BY to find non repeating ids.

SELECT id from 
       ( SELECT userid_one AS id FROM `friends` 
         WHERE `userid_two` = '$UserId' AND `requeststatus` ='accepted'

         UNION 

        SELECT userid_two AS id FROM `friends` 
        WHERE  `userid_one` =  '$UserId' AND `requeststatus` ='accepted'

        UNION ALL

        SELECT DISTINCT  `userid`  AS id FROM  `groupmembers` WHERE  `groupid` =24
       ) A

        GROUP BY id having COUNT(1)=1

Upvotes: 0

Ramgy Borja
Ramgy Borja

Reputation: 2458

try this 

SELECT * FROM (

SELECT userid_one AS id FROM `friends` WHERE `userid_two` = '$UserId' AND `requeststatus` ='accepted'
                    UNION 
SELECT userid_two AS id FROM `friends` WHERE  `userid_one` =  '$UserId' AND `requeststatus` ='accepted'

) as t ORDER BY id

Upvotes: 0

Pac0
Pac0

Reputation: 23149

Give your first results (the union) an alias of A, Give your second results an alias of B,

Then you can do :

SELECT id 
WHERE ((id IN A) AND NOT (id IN B)) OR (NOT (id IN A) AND (id IN B))
FROM (A UNION B)

Upvotes: 1

NikNik
NikNik

Reputation: 2301

Try with this query:

select id
from (your union)
group by id
having count(*) = 1

Upvotes: 1

Related Questions