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 5 of 5
  1. #1
    Senior Coder rafiki's Avatar
    Join Date
    Aug 2006
    Location
    Floating around somewhere...
    Posts
    2,043
    Thanks
    19
    Thanked 42 Times in 42 Posts

    Joining database tables

    ok im trying to normalise my data in my database
    http://i143.photobucket.com/albums/r...userstable.jpg
    http://i143.photobucket.com/albums/r...itemstable.jpg
    http://i143.photobucket.com/albums/r...ownedtable.jpg
    there are 3 images i made of what my tables will kind of need to look like,
    how would i go about making a statement to find out a) the admins items listed
    b) how many items if any owned?

  • #2
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,633
    Thanks
    4
    Thanked 148 Times in 139 Posts
    Your third table SHOULD NOT include the third or fourth columns at all. INAME and IDESCRIPTION do not belong in that table, they are found in the second table.

  • #3
    Senior Coder rafiki's Avatar
    Join Date
    Aug 2006
    Location
    Floating around somewhere...
    Posts
    2,043
    Thanks
    19
    Thanked 42 Times in 42 Posts
    ok so i took those two fields out of the equation, but how would i join them in a select statement to find out what items the admin owns, and how many?

  • #4
    Senior Coder rafiki's Avatar
    Join Date
    Aug 2006
    Location
    Floating around somewhere...
    Posts
    2,043
    Thanks
    19
    Thanked 42 Times in 42 Posts
    Code:
    SELECT u.uid, i.description, o.iid
    FROM users as u
    JOIN items as i
    JOIN owneditems as o
    ON u.uid = o.uid
    ive modified some code from previous thread to the table/field names on my new tableand added a JOIN as i need to connect three tables right? is that how

  • #5
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,633
    Thanks
    4
    Thanked 148 Times in 139 Posts
    you need to join users and items on a column from each of those tables. a three table join you need something like this:

    Code:
    select
    foo,
    bar,
    qux
    from
    table A
    INNER JOIN table B
    ON tableA.column = tableB.column
    INNER JOIN table C
    ON tableA.column = tableC.column
    note that last line could join tableB.column = tableC.column instead.


  •  

    Posting Permissions

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