Welcome to WebmasterWorld Guest from 54.167.110.211

Forum Moderators: open

Message Too Old, No Replies

MySQL query problem

Comparing 2 tables

     
1:20 am on Apr 4, 2008 (gmt 0)

Full Member

10+ Year Member

joined:May 21, 2003
posts:255
votes: 0


Hi, would appreciate some clues on how to do this with MySQL.

I have 2 tables in a database. Lets call them Table A (a table of usernames) and Table B (a table of transactions carried out by these usernames).

Over time some usernames have been deleted from Table A, but we did not remove their transactions from Table B. So we have Table B which has loads of transactions which no longer have corresponding users in Table A.

I want to identify and probably delete these orphan transactions! So tried "SELECT * FROM B, A WHERE B.username not like A.username"... and phpMyAdmin just hangs up with that :-(

Any ideas?! Do I need a JOIN of some sort? :-)

2:35 am on Apr 4, 2008 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Feb 27, 2001
posts:2547
votes: 0


How about something like

DELETE FROM B WHERE B.username IN (
SELECT DISTINCT B.username
FROM A RIGHT OUTER JOIN B ON username
WHERE A.username IS NULL
);

(paraphrasing here, back up your tables first ;) )
2:42 am on Apr 4, 2008 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Feb 27, 2001
posts:2547
votes: 0


By the way, the OUTER JOIN is the type of JOIN that you want because it includes records from one table that have no corresponding entry in the other.

References:

[dev.mysql.com...]

[en.wikipedia.org...]

[dev.mysql.com...]

3:00 am on Apr 4, 2008 (gmt 0)

Full Member

10+ Year Member

joined:May 21, 2003
posts:255
votes: 0


Thanks physics! I will look into this OUTER JOIN business... :-)
 

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

Featured Threads

Free SEO Tools

Hire Expert Members