I'm not sure if this is even possible, or if my JOIN-fu just isn't strong enough(it's pretty wimpy to tell you the truth). I have 3 tables which are tied together with a UID. I'm trying to get information from a result from all of them into one query, except I'm having trouble with making sure the result is what I want.
users
========= USERS ========== | uid | nickname | -------------------------- | testusr1 | Test User 1 | | testusr2 | Test User 2 | | testusr3 | Test User 3 | | testusr4 | Test User 4 | ============= GALLERY =========== | id | uid | ext | profile | --------------------------------- | 1 | testusr1 | png | 1 | | 2 | testusr2 | jpg | 1 | | 3 | testusr3 | png | 1 | | 4 | testusr4 | png | 1 | | 5 | testusr4 | jpg | 0 | ============= FRIENDS ============= | sender | reciever | status | ----------------------------------- | testusr1 | testusr3 | 0 | | testusr2 | testusr3 | 1 | | testusr2 | testusr1 | 1 | | testusr3 | testusr4 | 1 |
What I'm trying to do is get all of a user's friends. Friends are in the friends table where the status = 1. The uid can be either the sender or the reciever. In the table above, testusr3's friends are: testusr2 and testusr4
From here I want to snag the nickname from users, and the id from gallery WHERE profile = 1 AND uid = (that friend's ID).
So far, my query looks like:
$query = "SELECT u.uid AS USERID, g.id, g.ext, f.sender, f.reciever
FROM friends f
LEFT JOIN gallery g ON g.uid = f.sender AND g.profile = 1
LEFT JOIN users u ON u.uid = f.sender
WHERE f.status = 1
AND (f.sender = '$sentuid' OR f.reciever = '$sentuid')";
But, it labels all of the results as f.sender...and I'm pretty sure the g.profile = 1 isn't working. It does grab the friends accurately though. Any help would be greatly appreciated.
Best place to start is to get the matching records in a single column, with UNION. Then you have all the UIDs you need, in one place.
SELECT f.uid, u.nickname, g.id
FROM
(
(SELECT reciever as uid FROM friends where status=1 and sender='$sentuid')
UNION
(SELECT sender as uid FROM friends where status=1 and reciever='$sentuid')
) f
LEFT JOIN gallery g ON f.uid = g.uid and profile=1
LEFT Join users u ON f.uid = u.uid
Seems to me that you're really close, but a SQLfiddle would help. I believe you are right, g.profile = 1 is not working because you have no table reference for it, might as well take it out. But because of the join, you should be able to select it.
$query = "SELECT u.uid AS USERID, g.id, g.ext, g.profile, f.sender, f.reciever
FROM friends f
LEFT JOIN gallery g ON g.uid = f.sender
RIGHT JOIN users u ON u.uid = f.sender
WHERE f.status = 1
AND (f.sender = '$sentuid' OR f.reciever = '$sentuid')";