#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    8
    Rep Power
    0

    How do I join these tables so I don't get 100+ junk results?


    I'm working on problem #6 on this page pgrocer.net/Cis50/assign/quiztwo_F09.html

    and frankly I don't know where to begin. Queries I've tried have returned 168 results when I should be getting less than 10. I know I have to join the tables in some way but haven't figured out which ones to join and how to join them. Any and all help is appreciated.
  2. #2
  3. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Sep 2006
    Posts
    850
    Rep Power
    387

    Wink


    Originally Posted by AndroidZ
    I'm working on problem #6 on this page pgrocer.net/Cis50/assign/quiztwo_F09.html

    and frankly I don't know where to begin. Queries I've tried have returned 168 results when I should be getting less than 10. I know I have to join the tables in some way but haven't figured out which ones to join and how to join them. Any and all help is appreciated.
    1) Join orderz with ordline to get orders + line items

    2) Join orderz with invcust to get customer name

    3) Join ordline with inven to get item name
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    8
    Rep Power
    0
    Originally Posted by LKBrwn_DBA
    1) Join orderz with ordline to get orders + line items

    2) Join orderz with invcust to get customer name

    3) Join ordline with inven to get item name
    I'm still confused. Trying to work out how to join orderz to both orderline and invcust. Do I use natural join?
  6. #4
  7. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2012
    Location
    spaceBAR Central
    Posts
    229
    Rep Power
    42
    This is a simple example that lists orders by customer name, order date:
    Code:
    select ord.custid, cus.custname, ord.ordno, ord.ordate, orl.numord, orl.itemno, inv.itemname
     from  orderz ord join ordline orl on ord.ordno   =  orl.ordno
                      join inven   inv on orl.itemno  =  inv.itemno
                      join invcust cus on ord.custid  =  cus.custid
    order by cus.custname, ord.ordate

IMN logo majestic logo threadwatch logo seochat tools logo