| how do u I write this sql query [message #373479] |
Wed, 18 April 2001 16:46  |
Trady Las
Messages: 2
Registered: April 2001
|
Junior Member |
|
|
I have two tables Order and OrderItems.
The Order table has following columns
Order# (PK)
Item1
Item2
Item3
Item4
Customer
Date
for example
order# item1 item2 item3 item4 item5 customer Date
1000 2001 2002 2002 2004 2003 csco 2/12
1001 2002 2003 2001 2001 2002 msft 3/12
1002 2001 2001 2003 2002 2003 amg 4/12
1003 2002 2002 2004 2001 2001 brcm 4/11
The OrderItems table has following columns
Item(PK)
Name
for example
Item Name
2001 Pager
2002 Cell
2003 Beep
2004 Palm
2005 PlayStation
I need help to write a query to display records like this from Order table using OrderItem name:
order# item1 item2 item3 item4 item5 customer Date
1000 Pager Cell Cell Palm Beep csco 2/12
1001 Cell Beep Pager Pager Cell msft 3/12
1002 Pager Pager Beep Cell Beep amg 4/12
1003 Cell Cell Palm Pager Pager brcm 4/11
ie., show a result set with name from OrderItem table instead of item number in Order table.
Any help is greatly appreciated,
thanks
Trady
|
|
|
|
| Re: how do u I write this sql query [message #373481 is a reply to message #373479] |
Wed, 18 April 2001 18:36  |
Raj
Messages: 411
Registered: November 1998
|
Senior Member |
|
|
Do a self join for as many times as the item1,item2,item3.....columns translates to be selected.
SELECT
A.ORDER#,
B1.NAME,
B2.NAME,
B3.NAME,
B4.NAME,
A.CUSTOMER,
A.EFFDT
FROM ORDER_ITEM A, ORDER_ITEMS B1, ORDER_ITEMS B2,
ORDER_ITEMS B3, ORDER_ITEMS B4
WHERE B1.NAME IN (SELECT B11.NAME FROM ORDER_ITEMS B11
WHERE B11.ITEM = A.ITEM1
AND B11.NAME = B1.NAME)
AND B2.NAME IN (SELECT B21.NAME FROM ORDER_ITEMS B21
WHERE B21.ITEM = A.ITEM2
AND B21.NAME = B2.NAME)
AND B3.NAME IN (SELECT B31.NAME FROM ORDER_ITEMS B31
WHERE B31.ITEM = A.ITEM3
AND B31.NAME = B3.NAME)
AND B4.NAME IN (SELECT B41.NAME FROM ORDER_ITEMS B41
WHERE B41.ITEM = A.ITEM4
AND B41.NAME = B4.NAME)
|
|
|
|
| Re: how do u I write this sql query [message #373559 is a reply to message #373481] |
Mon, 23 April 2001 13:10 |
Trady Las
Messages: 2
Registered: April 2001
|
Junior Member |
|
|
Thanks for the query. The solution helps but the query skipping records where item number is null. I am sorry I did not specify this constraint when I posted it first time.
for example
order# item1 item2 item3 item4 item5 customer Date
1000 2001 2002 2004 csco 2/12
1001 2002 2003 2001 2001 2002 msft 3/12
1002 2001 2003 2002 2003 amg 4/12
1003 2003 2002 2004 2003 2001 brcm 4/11
1003 2004 2002 2001 2002 csco 4/10
1003 2002 2002 2003 2001 2001 brcm 4/09
in this case the query returns only records that matches the itemno from the order_items table and skipps record where item number is null.
Any suggestions
thanks
Trady
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
