Welcome to WebmasterWorld Guest from 54.163.40.152

Forum Moderators: open

Message Too Old, No Replies

MySQL query problem

Comparing 2 tables

     

isorg

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

10+ Year Member



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? :-)

physics

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

WebmasterWorld Senior Member 10+ Year Member



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 ;) )

physics

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

WebmasterWorld Senior Member 10+ Year Member



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...]

isorg

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

10+ Year Member



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

Featured Threads

Hot Threads This Week

Hot Threads This Month