...

View Full Version : Need help on better way of doing this query



maghiel
03-12-2007, 11:16 AM
(sorry for the bad title, couldnt come up with a good one ;))

i have 3 tables:



tbl1
-------
id
userid
info
-------


user
-------
id
groupid
-------



group
-------
id
-------


Now I need to get tbl1.info for all the users with the same groupid set.
I currently do this using a loop, but im sure it could be done better.
Anyone has any idea on this?

the loop is somthing like



SELECT `id` FROM `user` WHERE `groupid` = '5';

// LOOP
SELECT `info` FROM `tbl1` WHERE `userid` = 'resultfromthequeryeabove';
// END LOOP

phoenixshade
03-12-2007, 12:00 PM
Why is this three tables? It would make more sense to use a single table for all of this data, with the following fields:

id, userid, info, groupid

It seems to me that the first two tables will always have exactly one row for each user. The third table just makes me ask, "why?" Unless it has fields you didn't list, it doesn't seem to have any use.

In any event, try the following query:

SELECT tbl1.userid, tbl1.info, user.groupid
FROM user
LEFT JOIN tbl1
ON user.id=tbl1.userid
ORDER BY user.groupid
This should give you what you're looking for. If you want to get them for a single group, replace the last line with

WHERE user.groupid=groupnum

maghiel
03-13-2007, 12:13 PM
doh, i hate monday's lol.

and yeah, there's a lot more info in tbl group, just didn't want to bother you with that ;)

but thnx m8!



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum