Getting specific results from multiple tables in mysql

Question

This is kinda confusing, therefore I will explain my goal step by step, Below are the tables that I am working on (First Table, Second Table, and Third Table). First, I need to verify if the username from the Second Table does exist on the First Table, if not, remove the one that has no common username

The outcome should be something like this (George was excluded since it has no common username to First Table):

user_id | username
5423    | Bob
43      | Vicky

THEN I would like to verify by comparing the previous results above and the Third Table. My goal is to check if the user_id AND username from the previous results above has a common rows on the Third Table, if it does, EXCLUDE that one, and only retrieve the unique ones.

The OVERALL results should be the one below since 5423 | Bob does not exist yet on the Third Table:

OVERALL RESULTS:

user_id | username
5423    | Bob

First Table:

| username
| Bob
| Jessie
| Vicky

Second Table:

user_id | username
5423    | Bob
123     | Georgie
43      | Vicky

Third Table:

user_id | username
1       | Luke
54      | Stephenie
43      | Vicky

http://stackoverflow.com/questions/18443543/create-view-or-use-innerjoins might be of use — Daryl Gill, Oct 14 '13 at 01:59

John Woo · Accepted Answer · 2013-10-14T02:11:26.463

1

SELECT  b.*
FROM    FirstTable a
        INNER JOIN SecondTable b
            ON  a.username = b.username
        LEFT JOIN ThirdTable c
            ON  b.user_ID = c.user_ID 
WHERE   c.user_ID IS NULL

SQLFiddle Demo

The first join uses INNER JOIN so that only records that matches the condition will be on the result list. The second join uses LEFT JOIN so that all records from the left hand side table whether it has a matching row or not will be shown on the result list. Any non-matching rows will have a NULL value on the columns of the right hand side table.

Since you want to get only non matching records on the final result, you need to need to have WHERE clause that filters NULL values on the right hand side table.

edited Oct 14 '13 at 02:11

answered Oct 14 '13 at 01:59

John Woo

258,903
69
498
492

First Table does not have user_id, it just a list of username, therefore this line a.user_ID is invalid – user1973799 Oct 14 '13 at 02:09
You nailed it! Thanks bro, I appreciate this a lot! – user1973799 Oct 14 '13 at 02:15

score 0 · Answer 2 · answered Oct 14 '13 at 05:20

0

Try this:

SELECT  b.*
FROM    FirstTable a
        INNER JOIN SecondTable b
            ON  a.username = b.username
        LEFT JOIN ThirdTable c
            ON  b.user_ID = c.user_ID 
WHERE   b.`username` <> c.`username`

answered Oct 14 '13 at 05:20

Anand Solanki

3,419
4
16
27

Getting specific results from multiple tables in mysql

2 Answers2