Nerdrium Home

Macintosh

Linux

Web Design

Case Mods

Gardening

Photography

Recipes

 

My Blog

 

Facebook

Twitter

How to Select Distinct Data from Two Tables

by Michael Greifenkamp (February 1st, 2005)

This is sort of a lame excuse for an "article", but I needed this information yesterday and had to dig for it, and since this is my site and I can do what I want, you'll all just have to deal with the lameness....

Note: This presumes that you are using PHP and MySQL--I don't have the slightest idea whether this works in other version of SQL, etc....

Okay, now suppose that you have a set of e-mail addresses in a database table, and another set of e-mail addresses in another database table. Now also suppose that there are some addresses that reside in both tables. And you want a single list of addresses, without duplicates. The way to do it with MySQL is using the UNION statement. For the sake of simplicity, the field in both tables will be called "email" and the tables will be table1 and table2, respectively. Here's your query:

$query = "SELECT email FROM table1 WHERE email != '' UNION SELECT email FROM table2 WHERE email !=''";

That will give you a result set of distinct e-mail addresses from the two tables.

Heck, I'll even give the code for printing those addresses on-screen with a comma between each one so that you can then cut and paste the list into an e-mail program...

$query = "SELECT email FROM table1 WHERE email != '' UNION SELECT email FROM table2 WHERE email !=''";
$result = mysql_query($query);
$num = mysql_numrows($result);
 
if ($num < 1) { // no rows returned
  echo "<p>No rows returned.</p>"
}
else { // data was returned
  $i = 0;
  echo "<p>";
  while ($i < $num) { // write addresses...
    $email = mysql_result($result,$i,'email');
    echo "$email";
    $i++;
    if ($i != $num) { // write comma if not last in list...
      echo ",";
    } // end if $i != $num
  } // end while $i < $num
  echo "</p>";
} // end else data was returned

Now of course, I didn't do all of the mysql connection stuff (or mysql_close(); at the end either). And you can figure out the formatting yourself.

Send me an e-mail if you like.

--Michael