[RESOLVED] MySQL Join problems...

tekky

I need some SQL help... This aint working out for me...

SELECT * FROM {clan_members} cm
				 LEFT JOIN {clan_ranks} r ON r.rid=cm.rid
				 LEFT JOIN {users} u ON u.uid=cm.uid
				 WHERE cm.ClanID='{$clan->ClanID}' ORDER BY r.rClanID DESC, r.rid DESC, u.name ASC

Basically I need clan_ranks to return any matching ClanID's that match rid, but I need to make it JOIN r.rClanId=0 if r.rClanID is not equal to cm.ClanID.... currently it doesnt work... it returns a ton more rows with bad data (uid's of 0 or null instead of proper ones)....

Any ideas? I'm trying to get it done in 1 query if possible... but will goto 2 if I cant find a better solution... and just ignore the {} around table names, its a Drupal query string.

I've tried about 2hrs of tweaking the query in different ways... nothing got me closer than this, and its still wrong...

tekky

I sort of got it working...

SELECT * FROM {clan_members} cm 
					LEFT JOIN {clan_ranks} r ON r.rid=cm.rid
					LEFT JOIN {users} u ON u.uid=cm.uid
					WHERE cm.ClanID='{$clan->ClanID}' AND (r.rClanID=cm.ClanID OR r.rClanID='0')
					ORDER BY r.rClanID DESC, r.rid DESC, u.name ASC

This gets me a duplicate if there IS a rClanID for a clan, it gets their ID plus the 0 (default ID).. but thats workable... I'd definately be interested in a better solution. Basically with the sort, I take store the person's userID and compare it to the next one and ignore the 2nd if they are the same (meaning they had a real title + the default one returned).

Works... but always love 1 line solutions 🙂

tomhath

I'm guessing at what columns you really need since you have a SELECT *, but the easiest way to get rid of the duplicates is probably to pick the MAX clanID (add any other columns you use to both the SELECT and the GROUP BY):

SELECT r.rid, u.name, max(r.clanID) as clanID
...
GROUP BY r.rid, u.name
ORDER BY 3 DESC, 1 DESC, 2 ASC

tekky

if I take MAX() it wont only return the row of the MAX id only? If so that might work... but you are only taking into acct 2 tables when there are 3

clan_members
users
clan_ranks

clan_members containsd uid, clanID, rankId
users name, uid...
clan_ranks rankId rankName

I need all users from clan_members that are in ClanX, and then to get names, we join with users, and to get rankNames we join with clan_ranks....

thats what I'm aiming for.

cgraz

I'm new to JOINs, but here's a shot

SELECT m.uid, 
   m.name, 
   m.clanID, 
   m.rankId, 
   r.rankName 
FROM clan_members AS m 
INNER 
   JOIN users AS u 
      ON u.uid = m.uid 
INNER 
   JOIN clan_ranks AS r 
      ON r.rankId = m.rankId 
WHERE m.clanID = 4 
ORDER BY r.rankId DESC;

I wasn't sure if the ID in rankId and clanID are purposely different or not (the D is capitalized in one, lowercase in the other), but I wrote them as you had them in your post. Just replace 4 with whatever clanID you're looking for.

tekky

the MAX() idea worked... I though it would have just returned the MAX() in each row of data, not just returned the MAX row... learned something new 😃

Thanks Tomhath!