Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > PHP/MySQL > PHP Databases
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
PHP Databases Using PHP in conjunction with databases. PHP questions not specific to databases should be directed to one of the other PHP forums.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the PHP Databases section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old November 25th, 2004, 04:14 PM
Authorized User
 
Join Date: Jun 2003
Location: , , .
Posts: 43
Thanks: 0
Thanked 0 Times in 0 Posts
Default Select statement

Hello there, I am trying to select email addresses from table A and Table B wanting the results to be displayed taking out duplicate emails.

Currently I could see the duplicate emails in both tables via this query

Select t1.email, t2.email From tableA as t1, tableB as t2 WHERE t1.email = t2.email; That return the duplicates....

BUT

When i replace the "!=" in NOT equal comman in the place of the = command in the where clause it blows up on me...

What am i doing wrong?

Also what if I want to take tableA and TableB email address and throw them in a new table with out the duplicates, how can i do that?

Thank you for all your help....
Reply With Quote
  #2 (permalink)  
Old November 25th, 2004, 11:44 PM
Registered User
 
Join Date: Nov 2004
Location: , , .
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hey
First of all != works ..I tried and works for same query. Second thing is that can you explain your second question with example..I mean in table A and B contains only email id's or anything else ..some kind of different unique id etc?
saper

Reply With Quote
  #3 (permalink)  
Old November 26th, 2004, 10:15 AM
Authorized User
 
Join Date: Jun 2003
Location: , , .
Posts: 43
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Saper,
THanks for responding...

THe != does not work for me, it takes almost 15 minutes to return the results and the results are all duplicates which from a database of 7000 records, it returns almost 1,000,000 records back, thats just impossible... Furthermore, thats the exact query that i run...

select t1.email, t2.email FROM tableA as t1, tableB as t2 WHERE t1.email != t2.email;

Can you tell me if there is anything wrong with what I just wrote?
Moreover, in regards to the second part of my question, I want to throw the results that I received from my query into a new table renames to new_email_addressed, so how do i go about doing that... Also when I receive the results of my query in two coloumns(1st coloumn being results of t1, and second results of t2) how do i go about takeing the two results and inserting them into a temparay table? I don't really have an example cause i really can't understand how mysql can do it...

THanks for all your help.
Reply With Quote
  #4 (permalink)  
Old December 1st, 2004, 03:34 PM
Authorized User
 
Join Date: Dec 2004
Location: Tunkhannock, PA, USA.
Posts: 53
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to peg110
Default

Well to answer your first question you could use

  (SELECT DISTINCT t1.email FROM tableA as t1)
    UNION
  (SELECT DISTINCT t2.email FROM tableB as t2)


That should produce a UNIQUE list of ALL emails from both tables. You don't need to show both t1.email and t2.email on the same select (as your original shows). In both of your conditions you would either have the same email for BOTH t1 and t2 (when WHERE is an EQUALS) which is redundant, or you would have a huge (depending upon your db entries) list of completely different emails with each row (when the WHERE is NOT EQUAL). In this case you would be matching up EACH email (one at a time) from tableA with EACH (and EVERY) email from tableB . This is why your query (as you put it) blew up. It didn't blow up, it's just a lot of work, and of course you now have duplicates.

The use of the UNION and DISTINCT keywords in the SQL achieves (I believe) your result.

For your NEXT quesiton (Assuming you want ONLY email addresses in the new table) you could do this:

  INSERT INTO tableC
  (SELECT DISTINCT t1.email FROM tableA as t1)
    UNION
  (SELECT DISTINCT t2.email FROM tableB as t2)

This ASSUMES tableC is already created and can handle the data expected and that there is ONLY ONE column.

Good luck


Paul Gardner
------------------
PHP-LIVE help
Via Web @ http://www.mnetweb.co.uk/irc
Via IRC Client pgardner.net:6667
room #PHP
Reply With Quote
  #5 (permalink)  
Old December 14th, 2004, 04:08 PM
Authorized User
 
Join Date: Jun 2003
Location: , , .
Posts: 43
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Paul, thanks a bunch for your expertise and solution to my delimma...

Take care.
Dungey
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
select statement ??? RinoDM SQL Server 2000 7 June 19th, 2008 08:40 AM
select Statement gregalb SQL Server 2000 3 January 15th, 2008 12:00 AM
select statement help... RinoDM SQL Server 2000 13 January 10th, 2008 08:34 PM
Select from another select statement to a repeater simsen ASP.NET 2.0 Professional 0 May 2nd, 2007 04:34 PM
Select Statement jmss66 Oracle 1 May 27th, 2004 02:31 PM



All times are GMT -4. The time now is 07:53 AM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.