SitePoint Sponsor

User Tag List

Results 1 to 5 of 5

Thread: Joining Queries

  1. #1
    SitePoint Enthusiast
    Join Date
    Mar 2004
    Location
    Kentucky
    Posts
    64
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Joining Queries

    This was originally posted in the PHP forum but it was suggested I try the mysql forum.

    Ok, I have a table (table1) that contains the following:

    userid, username, pubpriv

    A sample entry would look like this:
    1, reefland, public

    I have a query that checks if pubpriv contains "public" and if so, a while function that will display all the usernames.

    Now in another table (table2) I have the following:

    userid, username, gallons

    A sample entry would look like this:
    1, reefland, 300

    Now what I want to do is if for each entry in table1 that is public, query table2 for that specific userid to show the gallons so my while function would look like:

    reefland - 300

    Any ideas how to make this work?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Code:
    select t2.username
         , t2.gallons
      from table1  as t1
    inner
      join table2  as t2
        on t1.userid
         = t2.userid  
     where t1.pubpriv 
         = 'public'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard wdmny's Avatar
    Join Date
    Jul 2000
    Location
    Here
    Posts
    1,010
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    First of all, your database layout is bad. Here is what I suggest you change it to:

    Users
    U_ID (auto index), U_Username (string), U_Public (integer)

    1, someuser, 0
    2, anotheruser, 1
    3, yetanother, 1
    4, andanother, 0

    Gallons
    G_UserID (integer), G_Gallons (integer)

    1, 100
    2, 92
    3, 350
    4, 51

    Now, I would use one query to pull all public users and their corresponding gallons data.

    Code:
    SELECT U_ID, U_Username, G_Gallons FROM Users
        LEFT JOIN Gallons ON Users.U_ID = Gallons.G_UserID
        WHERE (U_Public = 1)
    The returned records would be:

    2, anotheruser, 92
    3, yetanother, 350

    Edit:

    Ah, r937 beat me to post !

  4. #4
    SitePoint Enthusiast
    Join Date
    Mar 2004
    Location
    Kentucky
    Posts
    64
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Wes and Rudy,

    Thank you both for your replies, I will give the inner join a try.

    Wes,

    The db example I gave was very basic, the actual layout of the tables mentioned are a bit more complex than my example shown and has worked very well for me up until this query.

    rudy's suggestion worked great, thanks!
    Last edited by reefland; Jul 10, 2004 at 18:54.

  5. #5
    SitePoint Enthusiast
    Join Date
    Feb 2003
    Posts
    76
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I may be missing something, but from what I can distinguish from what you provided, you don't need two separate tables. Have one table with the following columns: id (int), username (varchar), approved (bool), gallons (int), using approved as the index.

    Code:
    SELECT username, gallons FROM table WHERE approved = 1


Bookmarks

Posting Permissions

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