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

    Join Date
    Oct 2012
    Posts
    8
    Rep Power
    0

    Mutliple table query returning duplicates with wrong values


    These are the tables I'm working with:
    Code:
    SQL> desc custinfo;
     Name                                      Null?    Type
     ----------------------------------------- -------- ----------------------------
     CUSTID                                             VARCHAR2(4)
     CUSTNAME                                           VARCHAR2(18)
     CUSTADR                                            VARCHAR2(12)
     CUSTCITY                                           VARCHAR2(10)
     CUSTSTATE                                          CHAR(2)
     CUSTZIP                                            VARCHAR2(5)
     CAPACITY                                           NUMBER(3)
     HOUSECODE                                          VARCHAR2(2)
    
    SQL> desc orderinfo;
     Name                                      Null?    Type
     ----------------------------------------- -------- ----------------------------
     CUSTID                                             VARCHAR2(4)
     DELDATE                                            DATE
     NUMGAL                                             NUMBER(3)
     GALPRICE                                           NUMBER(3,2)
    
    SQL> desc houseinfo;
     Name                                      Null?    Type
     ----------------------------------------- -------- ----------------------------
     HOUSECODE                                          VARCHAR2(2)
     AVGSUM                                             NUMBER(3)
     AVGFALL                                            NUMBER(3)
     AVGWINTER                                          NUMBER(3)
     AVGSPRING                                          NUMBER(3)
    
    
    
    CUST CUSTNAME           CUSTADR      CUSTCITY   CU CUSTZ   CAPACITY HO
    ---- ------------------ ------------ ---------- -- ----- ---------- --
    0874 Adam Brixton       348 Maple St Newport    RI 02840        275 1A
    1438 Jenna Marone       45 Main Rd   Fall River MA 02720        250 1B
    2314 Edward Fring       780 Ocean Dr Newport    RI 02840        300 2A
    4491 Liz Lemon          219 Fish Rd  Tiverton   RI 02878        250 2B
    2415 John Cabral        120 Brick Rd Fall River MA 02720        275 1B
    5123 Sally Singer       54 Chorus Dr Providence RI 02045        225 2A
    
    
    CUST DELDATE       NUMGAL   GALPRICE
    ---- --------- ---------- ----------
    0874 08-SEP-11        100        2.5
    1438 24-OCT-12         50       3.25
    2314 12-NOV-12         75        3.1
    4491 05-DEC-12        160       3.15
    2415 11-AUG-12         25       2.95
    5123 17-JUN-12         30       2.75
    
    
    HO     AVGSUM    AVGFALL  AVGWINTER  AVGSPRING
    -- ---------- ---------- ---------- ----------
    1A         30         60        150         75
    1B         35         65        160         80
    2A         40         70        170         85
    2B         45         75        180         90
    This is a homework problem I'm currently working on:
    Show the customer id, name and city for all customers that received a delivery of more than 150 gallons

    This is what I try:
    Code:
    SQL> select orderinfo.custid, custname, custcity
      2  from orderinfo, custinfo
      3  where numgal > 150;
    This is the result:
    Code:
    CUST CUSTNAME           CUSTCITY
    ---- ------------------ ----------
    4491 Adam Brixton       Newport
    4491 Jenna Marone       Fall River
    4491 Edward Fring       Newport
    4491 Liz Lemon          Tiverton
    4491 John Cabral        Fall River
    4491 Sally Singer       Providence
    I don't understand why the custid is the same for each customer, and why it's selecting every customer and not just those with more than 150 gallons ordered.

    Another homework problem in which I got similar junk results:
    For this one use the oil tables that you set up and use a subquery. Select the minimum average fall use from the house table. Then show all customers whose number of gallons delivered times two is greater than the minimum.

    I've been at it all day and am going in circles. I'd appreciate hints instead of the answer.
  2. #2
  3. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2012
    Location
    spaceBAR Central
    Posts
    229
    Rep Power
    42
    You need to tell the database how you want to join the two tables, Try this:
    Code:
    select orderinfo.custid, orderinfo.custname, orderinfo.custcity
     from  orderinfo natural join custinfo
    where  orderinfo.numgal  >  150;

    Also look at this info:joins
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    8
    Rep Power
    0
    Didn't seem to solve it. I'll continue working on it.
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    5
    Rep Power
    0

    You need the join


    Assuming numgal is the number of gallons received.

    select c.custid, c.custname, c.custcity
    from custinfo c join orderinfo o
    on c.custid = o.custid
    and o.numgal > 150
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    8
    Rep Power
    0
    Thank you very much, it worked. I didn't even know about the ON clause. Reading up on that now.

IMN logo majestic logo threadwatch logo seochat tools logo