Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    Regular Coder
    Join Date
    Jun 2004
    Posts
    130
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Need help on better way of doing this query

    (sorry for the bad title, couldnt come up with a good one )

    i have 3 tables:

    Code:
    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

    Code:
    SELECT `id` FROM `user` WHERE `groupid` = '5';
    
    // LOOP
    SELECT `info` FROM `tbl1` WHERE `userid` = 'resultfromthequeryeabove';
    // END LOOP

  • #2
    Regular Coder
    Join Date
    Feb 2007
    Location
    near Washington, DC
    Posts
    135
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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:
    Code:
    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
    Code:
    WHERE user.groupid=groupnum
    Last edited by phoenixshade; 03-12-2007 at 01:02 PM.
    — Wilford Nusser
    Validate Your Code: (X)HTML CSS
    An HTML Email is NOT a Web Page: HTML Email Guide (1.2Mb pdf) Webmail CSS Support
    REGEX: Brought to you by Psychotic Crack-Smoking Monkeys

  • #3
    Regular Coder
    Join Date
    Jun 2004
    Posts
    130
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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!


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •