homepage Welcome to WebmasterWorld Guest from 54.205.197.66
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
MySQL query problem
Comparing 2 tables
isorg




msg:3618633
 1:20 am on Apr 4, 2008 (gmt 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? :-)

 

physics




msg:3618671
 2:35 am on Apr 4, 2008 (gmt 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 ;) )

physics




msg:3618673
 2:42 am on Apr 4, 2008 (gmt 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...]

isorg




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

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

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.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved