#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2000
    Posts
    4
    Rep Power
    0
    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.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 1999
    Location
    Netherlands
    Posts
    77
    Rep Power
    15
    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

  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2000
    Posts
    4
    Rep Power
    0
    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?
  6. #4
  7. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2000
    Posts
    4
    Rep Power
    0
    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!

Similar Threads

  1. Replies: 8
    Last Post: September 25th, 2003, 04:11 AM
  2. Joining multiple tables without a 1-to-1 match
    By unixman in forum MySQL Help
    Replies: 2
    Last Post: August 16th, 2003, 09:39 AM
  3. joining tables
    By tamara in forum MySQL Help
    Replies: 2
    Last Post: July 31st, 2003, 07:30 PM
  4. joining multiple tables and sorting results
    By Cira9999 in forum MySQL Help
    Replies: 5
    Last Post: August 18th, 2002, 09:24 PM
  5. joining info from two tables to a third
    By cmc in forum MySQL Help
    Replies: 3
    Last Post: July 29th, 2002, 08:39 PM

IMN logo majestic logo threadwatch logo seochat tools logo