October 13, 2003 at 2:50 pm
I have an assignmetn that asks this question:
For each order, list the ORDER_NUM, ORDER_DATE, PART_NUMBER, PART_DESCRIPTION, ITEM_CLASS for each part that makes up the order.
It needs data from the following tables
ORDER (top level)
ORDER_LINE (details)
PART (part information)
I currently get about 90 rows of data but i should only have a bout 10 or 15. Here is the code i have, perhaps you can help me.
SELECT ORDER_LINE.ORDER_NUM, ORDERS.ORDER_DATE, PART.PART_NUM, PART.DESCRIPTION, PART.CLASS
FROM ORDERS, ORDER_LINE, PART
WHERE ORDER_LINE.ORDER_NUM = ORDERS.ORDER_NUM
Do i need a subquery? Or to use the join?
I am lost. Thanks.
October 13, 2003 at 2:58 pm
I put this in and it returns 9 rows. Is this right? It seems to be for me! But what do I know! 🙂
SELECT ORDER_LINE.PART_NUM, PART.DESCRIPTION, PART.CLASS, ORDERS.ORDER_DATE
FROM ORDER_LINE, PART, ORDERS
WHERE ORDER_LINE.PART_NUM = PART.PART_NUM AND ORDER_LINE.ORDER_NUM = ORDERS.ORDER_NUM
October 13, 2003 at 4:06 pm
quote:
I put this in and it returns 9 rows. Is this right? It seems to be for me! But what do I know! 🙂SELECT ORDER_LINE.PART_NUM, PART.DESCRIPTION, PART.CLASS, ORDERS.ORDER_DATE
FROM ORDER_LINE, PART, ORDERS
WHERE ORDER_LINE.PART_NUM = PART.PART_NUM AND ORDER_LINE.ORDER_NUM = ORDERS.ORDER_NUM
Looks good, but it will be easier to read if you use table aliases and the JOIN syntax:
SELECT l.Part_Num, p.Description, p.Class, o.Order_Date
FROM Order_Line l JOIN Part p ON l.Part_Num = p.Part_Num
JOIN Orders o ON l.Order_Num = o.Order_Num
--Jonathan
--Jonathan
October 14, 2003 at 6:25 pm
mcmcom,
Why do you keep trying to get us to do your homework for you? tsk tsk 🙂
The different between the 1st query and the 2nd query is that the 1st query is missing the join syntax for the PartNumber, effectively creating a cross join, I guess.
As Jonathan said, using ANSI syntax will keep you from making silly mistakes like this. This isn't only highly recommended, it's almost mandatory. The syntax you're using is Microsoft specific, and they aren't going to continue supporting it forever, as the ANSI syntax is OBVIOUSLY better.
To demonstrate, your 1st query would look like this in ANSI:
SELECT ORDER_LINE.ORDER_NUM, ORDERS.ORDER_DATE, PART.PART_NUM, PART.DESCRIPTION, PART.CLASS
FROM ORDERS
JOIN ORDER_LINE on ORDER_LINE.ORDER_NUM = ORDERS.ORDER_NUM
JOINPART
this won't even compile, as there is no join to the PART table. Now, your 2nd query would look like this:
SELECT ORDER_LINE.ORDER_NUM, ORDERS.ORDER_DATE, PART.PART_NUM, PART.DESCRIPTION, PART.CLASS
FROM ORDERS
JOIN ORDER_LINE on ORDER_LINE.ORDER_NUM = ORDERS.ORDER_NUM
JOINPARTon PART.Part_Num = ORDER_LINE.Part_Num
You see what I'm saying? Much easier to de-bug. Whoever is teaching you to write in the old syntax is silly.
Signature is NULL
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply