Helpful Information
 
 
Category: MySQL Help
joining similar tables

How to get all unique (distinct) rows from more than one table, having at least one equal column name?
For example: table1 and table2 have same columns, table1 has 10 rows, table2 has 20 rows. 5 of them also exist in table1, but i want the 35 distinct rows from both tables combined.

Hi Marc,

What you want is a "difference" query between both tables, in which you get the 15 unique rows?
I assume you set up both tables with an unique ID column. Now to get the 15 non-overlapping rows from both tables, do:

select * from Table1 t1, Table2 t2 where t1.ID <> t2.ID

Peter

Well, no, this is not what I meant.
I do have unique id's in both table's, but both table's also contain an email field.
What I want is al rows from both tables with distinct emails (no duplicates). There may be duplicate id's, since the tables are unrelated. Actually I need to 'virtually' concatenate the tables and then select the distinct emails.
How do I do this?

Found it!
I just bought the book "MySQL" by Paul DuBois (New Riders,ISBN 0-7357-0921-1) and there it was: under the heading "Solutions Miscellany" (p. 167) I read that what I need was a UNION, but mySQL doesn't support that.
The workaround is to create a temporary table and insert records into it from my tables. Then do a select on the temporary table and drop it when finished.
Thanks Paul!










privacy (GDPR)