1

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.

2
  • For id 1, do you want to get 3 or 4? Or both?
    – oNare
    Oct 3 '15 at 19:20
  • Both. And for id 2 I would like to get 3...
    – Branko
    Oct 3 '15 at 19:21
0

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

5
  • This query gives me almost same result as my second query (usersids). I need basic information from first_table for "usersids" and I have to use username="johnie"
    – Branko
    Oct 3 '15 at 19:30
  • Can you add your expected output in your question?
    – oNare
    Oct 3 '15 at 19:31
  • where username="johnie".... Output is now the same as select * from first_table?
    – Branko
    Oct 3 '15 at 19:41
  • 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.
    – Branko
    Oct 3 '15 at 19:45
  • I've update the answer, try now.
    – oNare
    Oct 3 '15 at 19:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.