CODEWITHSUNDEEP
javamysqlsql-servernetbeansderby
Cj280
Cj280

Reputation: 23

combining multiple SQL tables

I'm trying to create a friends list when a user logs into my java applications. I have 3 tables, USERS, FRIENDS and SONGS.

The user table:

ID| USERNAME | DOB      |
:-| -------- | -------- |
1 | Ted      |01/01/1990|
2 | Jim      |02/03/1990|
3 | Bill     |03/03/1990|

The friend tables (ID = ID in USER table)

USER_ID| FRIEND_ID |
:------| --------- |
1      | 2         |
2      | 1         |
1      | 3         |
3      | 1         |

Songs table(again ID = USER)

ID| ARTIST    | TITLE            |
:-| --------- | ---------------- |
2 | Queen     | We will rock you |
2 | KISS      | Crazy nights     |
2 | Aerosmith | Jaded            |

So what I need to do is when the user logs in, select all of their friends from the friends table, get all the information about these friends, and also select every song belonging to the friend.

currently I have some sql which will select the friend and their info correctly however I can't manage to combine this with getting the songs.
Ideally I want to loop through get each users details (including the songs in an arraylist) then create an array of the user object.

SQL I'm using to get the friends: 

SELECT Username, DOB, POB FROM USERS WHERE ID in (SELECT FRIEND_ID FROM 
ADMIN1.FRIENDSHIPS Where USER_ID in (Select ID FROM USERS WHERE USERNAME = ?

?= the user who logs in.

Upvotes: 2

Views: 113

Answers (2)

Wintergreen
Wintergreen

Reputation: 234

 select u.ID,f.FRIEND_ID,fsong.ARTIST
  from user u 
 inner join friend f on u.ID=f.USER_ID
 left join user fsong on fsong.ID= f.FRIENDID

Upvotes: 0

anty
anty

Reputation: 322

You should use JOINs like this:

SELECT * FROM users
LEFT JOIN friends ON friends.user_id = users.id
LEFT JOIN songs ON songs.id = friends.friend_id
WHERE users.id = ?

users, friends, and songs are your table-names.

Why LEFT JOIN? Because you don't want to remove users without any friends, or friends without any songs.

Upvotes: 1

Related Questions