CODEWITHSUNDEEP
mysql
Kris1511
Kris1511

Reputation: 300

Select Union Query?

I have two tables:

Table A:

id  name
------------    
1   Scott
2   Dan
3   Sam

Table B:

id  name
------------    
1   Dan
2   Andi
3   Jess

My result needs to be:

Id Name  Found
1  Scott A
2  Dan   C i.e. found in both
3  Sam   A
2  Andi  B
3  Jess  B

I am able to do a UNION to fetch the result but how do I create the Found column?

Upvotes: 2

Views: 387

Answers (5)

Jonathan Park
Jonathan Park

Reputation: 775

Wrong answer You are looking for "union distinct" instead of just union. Corrected when I got the smack down and realized I mis read the question.

Generate a bitmask as below, using simple power notation to make it clearer that im just inserting ingrementing powers of 2 for the bitmask.

with data as (
SELECT Id,Name, 2^0 as bitmask FROM A
UNION ALL 
SELECT Id,Name, 2^1 as bitmask FROM B
UNION ALL 
SELECT Id,Name, 2^2 as bitmask FROM C)
SELECT Id,Name, SUM(bitmask)
FROM data
GROUP BY Id,Name

Upvotes: -1

eykanal
eykanal

Reputation: 27017

It looks to me like you effectively want to simultaneously do a left and right join. This technically isn't possible, since you always need to have one reference table. The only way I can think of doing this would be the following:

SELECT tableA.*
  LEFT JOIN tableB.* USING name
UNION DISTINCT SELECT tableB.*
  LEFT JOIN tableA USING name

After thinking some more, you may also be able to do:

SELECT tableA.*
  LEFT JOIN  tableB.* USING name
  RIGHT JOIN tableB.* USING name

...although I'm not sure that's valid.

Upvotes: 0

Khorkrak
Khorkrak

Reputation: 4019

select tmp.name, case count(*) when 1 then tmp.tbl else 'C' end found 
  from (select id, name, 'A' tbl from TableA 
        union all 
        select id, name, 'B' tbl from TableB) as tmp 
 group by tmp.name;

+-------+-------+
| name  | found |
+-------+-------+
| Andi  | B     |
| Dan   | C     |
| Jess  | B     |
| Sam   | A     |
| Scott | A     |
+-------+-------+

Upvotes: 0

OMG Ponies
OMG Ponies

Reputation: 332571

Use:

  SELECT CASE 
           WHEN y.name IS NULL THEN z.id
           WHEN z.name IS NULL THEN y.id
           ELSE y.id
         END AS id,
         x.name,
         CASE 
           WHEN y.name IS NULL THEN 'B'
           WHEN z.name IS NULL THEN 'A'
           ELSE 'C'
         END AS found
    FROM (SELECT a.name
            FROM TABLE_A a
          UNION 
          SELECT b.name
            FROM TABLE_B b) x
LEFT JOIN TABLE_A y ON y.name = x.name
LEFT JOIN TABLE_B z ON z.name = x.name

Alternative:

  SELECT COALESCE(y.id, z.id) AS id,
         x.name,
         CASE 
           WHEN y.name IS NULL THEN 'B'
           WHEN z.name IS NULL THEN 'A'
           ELSE 'C'
         END AS found
    FROM (SELECT a.name
            FROM TABLE_A a
          UNION 
          SELECT b.name
            FROM TABLE_B b) x
LEFT JOIN TABLE_A y ON y.name = x.name
LEFT JOIN TABLE_B z ON z.name = x.name

Upvotes: 4

Mark Byers
Mark Byers

Reputation: 838226

The way to do this is to use a FULL OUTER JOIN, but since this is not supported in MySQL you can instead use a combination of a LEFT JOIN and INNER JOIN and RIGHT JOIN.

(
    SELECT A.Id, A.Name, 'A' AS Found
    FROM A LEFT JOIN B ON A.Name = B.Name
    WHERE B.Name IS NULL
)
UNION ALL
(
    SELECT B.Id, B.Name, 'B' AS Found
    FROM A RIGHT JOIN B ON A.Name = B.Name
    WHERE A.Name IS NULL
)
UNION ALL
(
    SELECT A.Id, A.Name, 'C' AS Found
    FROM A JOIN B ON A.Name = B.Name
)

In fact, you only need a LEFT and RIGHT JOIN because you can handle the INNER JOIN at the same time as you do one of the other two joins. I think the above demonstrates the principle more clearly, but in practice the following will give better performance:

SELECT A.Id, A.Name, IF(B.Name IS NULL, 'A', 'C') AS Found
FROM A LEFT JOIN B ON A.Name = B.Name
UNION ALL
SELECT B.Id, B.Name, 'B' AS Found
FROM A RIGHT JOIN B ON A.Name = B.Name
WHERE A.Name IS NULL

Result:

Id  Name   Found
1   Scott  A    
2   Dan    C    
3   Sam    A    
2   Andi   B    
3   Jess   B

Upvotes: 1

Related Questions