| Author |
Topic |
|
kandy
Starting Member
15 Posts |
 Posted - 2006-10-04 : 13:56:26
|
| I need to find the number and name of each customer(from customer table) that currently has an order for a Gas Range.(from part table). Part table and customer table dont have common attributes to be joined. There are two other tables called order_line and orders .order_line and customer have a comoon attribute.orders and customer have a common attribute.. how do i join these tables ? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-10-04 : 14:21:43
|
| So how would you know if a customer has an order for a gas range then? Even if they don't have common columns, there has got to be a way to relate them.Tara Kizer |
 |
|
|
kandy
Starting Member
15 Posts |
Posted - 2006-10-04 : 14:29:06
|
| These are the tables im working with.I still cant figure out how to join them.i tried joining rep ,customer,orders and order_line but it dint give the correct result. TABLE REP(REP_NUM CHAR(2) PRIMARY KEY,LAST_NAME CHAR(15),FIRST_NAME CHAR(15),STREET CHAR(15),CITY CHAR(15),STATE CHAR(2),ZIP CHAR(5),COMMISSION NUMBER(7,2),RATE NUMBER(3,2) ); TABLE CUSTOMER(CUSTOMER_NUM CHAR(3) PRIMARY KEY,CUSTOMER_NAME CHAR(35) NOT NULL,STREET CHAR(15),CITY CHAR(15),STATE CHAR(2),ZIP CHAR(5),BALANCE NUMBER(8,2),CREDIT_LIMIT NUMBER(8,2),REP_NUM CHAR(2) ); TABLE PART(PART_NUM CHAR(4) PRIMARY KEY,DESCRIPTION CHAR(15),ON_HAND NUMBER(4,0),CLASS CHAR(2),WAREHOUSE CHAR(1),PRICE NUMBER(6,2) ); TABLE ORDERS(ORDER_NUM CHAR(5) PRIMARY KEY,ORDER_DATE DATE,CUSTOMER_NUM CHAR(3) );TABLE ORDER_LINE(ORDER_NUM CHAR(5),PART_NUM CHAR(4),NUM_ORDERED NUMBER(3,0),QUOTED_PRICE NUMBER(6,2),PRIMARY KEY (ORDER_NUM, PART_NUM) ); |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-10-04 : 14:48:19
|
| You haven't provided enough information for us to help. Could you show us with a data example how you would logically relate the data? Please show us a few rows from each table and what you would like the output of your query to show using these rows.Tara Kizer |
|
|
|
kandy
Starting Member
15 Posts |
Posted - 2006-10-04 : 15:20:37
|
| INSERT INTO PART VALUES('DR93','Gas Range',8,'AP','2',495.00);INSERT INTO PART VALUES('FD21','Stand Mixer',22,'HW','3',159.95);INSERT INTO PART VALUES('KL62','Dryer',12,'AP','1',349.95);INSERT INTO PART VALUES('KV29','Treadmill',9,'SG','2',1390.00);INSERT INTO ORDER_LINEVALUES('21610','DR93',1,495.00);NSERT INTO ORDER_LINE VALUES('21608','AT94',11,21.95);customer and order_line tables have part_num as common attribute..as u can see part_num DR93 in the above tables.But what is needed is customer_num and customer_name from the customer table which currently have an order for gas range.INSERT INTO CUSTOMER VALUES('356','Ferguson''s','382 Wildwood','Northfield','FL','33146',5785.00,7500.00,'65');INSERT INTO CUSTOMER VALUES('408','The Everything Shop','1828 Raven','Crystal','FL','33503',5285.25,5000.00,'35');INSERT INTO REP VALUEs('35','Hull','Richard','532Jackson','Sheldon','FL','33553',39216.00,0.07);rep and customer table have rep_num as the common attribute.(rep_num 35) |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-10-04 : 19:02:49
|
1. You did not post any sample data for table ORDERS2. Is the on MS SQL Server ? As there isn't a datatype DATE or NUMBER in MS SQL ServerAnyway here it isselect *from PART p inner join ORDER_LINE l on p.PART_NUM = l.PART_NUM inner join ORDERS o on l.ORDER_NUM = o.ORDER_NUM inner join CUSTOMER c on o.CUSTOMER_NUM = c.CUSTOMER_NUM
KH |
|
|
|
kandy
Starting Member
15 Posts |
Posted - 2006-10-05 : 14:22:32
|
| the query works.but i have an additional row generated.probably,its the duplicate order_num in the order_line table.there are two entries in the order_line table with same order_num .how do i eliminate this duplicate? i tried using unique..doesnt work. |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-10-05 : 18:30:40
|
Please provide the sample data for all tables and the result that you want.
KH |
|
|
|
|
|
|
joining unrelated tables