MySQL Multiple Join/Subquery Questions

Question

Hi All,

For about a year now, we’ve been allowing our users to login with usernames and/or email addresses that are not unique (though each user does have a unique id). Although the system handles duplicate usernames/emails elegantly, we’ve decided to finally enforce unique usernames and email addresses. I’ve been tasked with generating a table in MySQL that will show the duplicates and the tables in which a duplicate’s id is being used (i.e. the tables dependent on the duplicate’s user id, using 1 for true and 0 for false). This table will then be used as a reference once duplicate data is marked for deletion. In short, I’m looking to generate a table something like this:

| User_id | Username | Email | Exists_in_Table1 | Exists_in_Table2 | Exists_in_Table3 |
-----------------------------------------------------------------------------------------------------------
| 0001.....| test1.........| email.| 0..........................| 0..........................| 1..........................|
| 0002.....| test2.........| email.| 0..........................| 1..........................| 1..........................|
| 0003.....| test3.........| email.| 1..........................| 1..........................| 1..........................|

It doesn’t matter much how this is accomplished. Since my SQL skills are somewhat lacking, I intended to do this programmatically using PHP and a number of simple SQL queries. However, I believe a single SQL query or a series of queries (without the use of PHP) is the cleanest approach. I know how to query for duplicates, but I can’t seem to figure out how to query multiple tables and join them by the user id in an appropriate manner. I appreciate any and all help with this. Thank you.

Answer 1

SELECT u.User_id, u.Username, u.Email,
 IF(t1.User_id IS NULL, 0, 1) AS Exists_in_Table1,
 IF(t2.User_id IS NULL, 0, 1) AS Exists_in_Table2,
 IF(t3.User_id IS NULL, 0, 1) AS Exists_in_Table3
FROM Users u
LEFT OUTER JOIN Table1 t1 USING (User_id)
LEFT OUTER JOIN Table2 t2 USING (User_id)
LEFT OUTER JOIN Table3 t3 USING (User_id);