Mysql – I have two tables and need to join them

join;MySQL

First table:

id | username | firstname |  surname  | address
---+----------+-----------+-----------+---------
1  | johnie   | john      | something | blabla
2  | alice    | something | something | blabla
3  | bar      | cola      | something | blabla
4  | whiskey  | whiskey   | something | blabla

Second table:

aincrement | id | user_id
-----------+----+---------
1          | 1  | 3
2          | 1  | 4
3          | 2  | 3

If I do 

SELECT username, firstname, surname 
FROM first_table 
WHERE username != "johnie"

I get all the rows except johnie…

If I do:

SELECT DISTINCT T2.user_id 
FROM second_table AS T1 
JOIN second_table AS T2 ON T1.id = T2.id 
WHERE T1.id = 1 AND T2.user_id <> 1

I get 3,4

But i would like to get 3 and 4 with all the others details (username, firstname, surname….)

My expected output 

id | username | firstname |  surname  | address
---+----------+-----------+-----------+---------
3  | bar      | cola      | something | blabla
4  | whiskey  | whiskey   | something | blabla

I have to use where username="johnie" (first_table). That user has id 1. Then I need to obtain user_id(s) from second_table (3, 4) and in output I need only basic info for users with id 3 and 4.

Best Answer

You could use GROUP BY, if you only need the basic information from first_table:

SELECT
    T1.id,
    T1.username,
    T1.firstname,
    T1.surname,
    T1.address
FROM first_table AS T1
JOIN second_table AS T2 ON (T2.user_id=T1.id)
JOIN first_table AS T3 ON (T3.id=T2.id AND T3.id<>T2.user_id)
WHERE T3.username='johnie' 
GROUP BY T1.id;

Try it in SQLFiddle.

You can also test it with T3.username='alice'.

Related Question