Hey guys I have a ques开发者_JAVA技巧tion which is how to get information from one tables and then with that info e.g. a name and then query the database to tell me a value in table 2.
So in short I have a friend system going but I cant get the pictures for that user because it is in another table. I can get his name but i can not get his picture. So how do you get his picture which is in table 1 and the name is in table 2.
table 1
name Users
id username password Email picture theme
1 bob dabsd2323ty hjas@hsdf uploads/adsda.png themes/theme1.png
2 fred dabsd2323ty hjas@hsdf uploads/adsda.png themes/theme1.png
table 2
name Friends
id sender receiver checked
1 bob fred 2
2 means he has checked it and accepted it.
so i can get the name from table 2 but i dont know how to get the picture for the user from table 1
This is a classic JOIN query. Whose picture do you want, the sender's or the receiver's? Let's get the picture for the receiver:
SELECT sender, receiver, picture
FROM Friends JOIN Users ON(receiver = username)
WHERE checked = 2;
If you want a specific user only, add the condition sender = 'Jim'
or something like that to the query.
You can also JOIN a table twice. Suppose you want to get the picture of both the sender and the receiver:
SELECT sender, receiver, U1.picture AS senderpicture, U2.picture AS receiverpicture
FROM Friends
JOIN Users AS U1 ON(sender = U1.username)
JOIN Users AS U2 ON(receiver = U2.username)
WHERE U2.checked = 2;
SELECT T1.PICTURE
FROM TABLE2 T2
JOIN TABLE1 T1
ON T1.USERNAME = T2.SENDER
You need a join, probably a left join:
SELECT cols, you, want FROM Friends LEFT JOIN Users ON Friends.username=Users.username;
精彩评论