MySql Union only return values from BOTH

Question

Results 1
   Zac
   Dave
   Ned

Results 2
   Eric
   Mark
   Zac

This is the output from a select query.

select names from table where id=1 UNION select names from tables where id=2;

I want to select all from these results that are contained in both results. Union returns all the names (Zac only once). How do I get the query to only return Zac?

You're looking for an *intersection*, not a union. See this question: [Intersect in mysql](http://stackoverflow.com/questions/2621382/intersect-in-mysql) — Dan J, Mar 20 '13 at 17:18

score 1 · Answer 1 · answered Mar 20 '13 at 17:24

1

This should do it:

SELECT name FROM table1
INNER JOIN table2
USING (name)

Result

| NAME |
--------
|  Zac |

See the demo

answered Mar 20 '13 at 17:24

Kermit

33,827
13
85
121

MySQL does not have an INTERSECT operator, unfortunately. – Dan J Mar 20 '13 at 17:25
@DanJ Whoops; updated. – Kermit Mar 20 '13 at 17:26

score 0 · Answer 2 · answered Mar 20 '13 at 17:37

0

Just use an INNER JOIN between the two tables.

SELECT a.name
FROM table1 AS a
JOIN table2 AS b ON a.name = b.name

answered Mar 20 '13 at 17:37

judda

3,977
1
25
27

MySql Union only return values from BOTH

2 Answers2