Mutliple table query returning duplicates with wrong values

Dev Shed Forums Sponsor:

October 20th, 2012, 01:59 PM

AndroidZ

Registered User

Join Date: Oct 2012

Posts: 8

Time spent in forums: 1 h 44 m 49 sec
Reputation 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.

October 20th, 2012, 08:04 PM

spacebar208

Contributing User

Join Date: Apr 2012

Location: spaceBAR Central

Posts: 191

Time spent in forums: 2 Days 9 h 54 m 56 sec
Reputation Power: 41

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

October 22nd, 2012, 03:39 PM

AndroidZ

Registered User

Join Date: Oct 2012

Posts: 8

Time spent in forums: 1 h 44 m 49 sec
Reputation Power: 0

Didn't seem to solve it. I'll continue working on it.

October 28th, 2012, 10:38 AM

soniya_dawle

Registered User

Join Date: Oct 2012

Posts: 4

Time spent in forums: 1 h 4 m 25 sec
Reputation 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

October 31st, 2012, 12:47 PM

AndroidZ

Registered User

Join Date: Oct 2012

Posts: 8

Time spent in forums: 1 h 44 m 49 sec
Reputation Power: 0

Thank you very much, it worked. I didn't even know about the ON clause. Reading up on that now.

Mutliple table query returning duplicates with wrong values

Developer Shed Advertisers and Affiliates