homepage Welcome to WebmasterWorld Guest from 54.198.148.191
register, free tools, login, search, subscribe, help, library, announcements, recent posts, open posts,
Subscribe to WebmasterWorld
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
MySQL - 3 columns with same data type in different tables
Avoiding duplicate results
Readie




msg:4307349
 2:45 pm on May 3, 2011 (gmt 0)

An incomplete site has recently come to me to complete development on, and I'm having a bit of an issue with this one.

This site stores Email addresses in 3 different database tables depending on how the user registered.

If the user registered via Facebook, the user's Email address is stored under `email` in `ph_user`.

If the user registered through the site as a normal user, the user's email is stored under `username` in `ph_standard_auth`.

If the user registered through the site as a business owner, the email is stored under `email` in `ph_listing`.

A single address could appear in 1 or 2 (not all 3) of these tables at the same time.

My client has asked me to generate a CSV file from these 3 tables containing the email, first name and last name of every user, with the email address being unique.

There are currently just under 2 million rows available between these 3 tables, and it is only going to increase, so I cannot do a dirty hack of a sub-query to check the existence in a table I have already retrieved the data from, and I cannot have a "where not in()" generated through a PHP array either. It would just be too slow.

So, if anyone here has ever had an issue of this sort arise before, could you please share any information you have that might help? :)

Thanks in advance,

Readie

 

LifeinAsia




msg:4307389
 3:48 pm on May 3, 2011 (gmt 0)

UNION should work for you. However, if there are any instances of where the first and last names do not match exactly between the tables (maybe the business owner added his middle initial to one of them), you'll get multiple instances of the e-mail address.

Readie




msg:4307682
 8:44 am on May 4, 2011 (gmt 0)

This works perfectly, thanks very much :)

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About
© Webmaster World 1996-2014 all rights reserved