In search of help....only need opinions!

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

In search of help....only need opinions!

Author

Topic

dlmagers10
Starting Member

48 Posts

Posted - 2010-10-16 : 00:47:04

SELECT ORDER_NUM, ORDER_DATE
FROM ORDERS, CUSTOMER
WHERE CUSTOMER.CUSTOMER_NAME = 'Johnson Department Store'
UNION
SELECT ORDER_LINE.ORDER_NUM, ORDERS.ORDER_DATE
FROM ORDERS, ORDER_LINE
WHERE ORDER_LINE.PART_NUM = 'DR93';

looking for order_num and order_date for each order that was placed by Johnson Department Store that also contains an order_line for a Gas Range (GAS RANGE PART_NUM IS 'DR93'.

Up above is what I have so far.

TABLES INCLUDED:

ORDERS ~
ORDER_NUM, ORDER_DATE, CUSTOMER_NUM

ORDER_LINE ~
ORDER_NUM, PART_NUM, NUM_ORDERED, QUOTED_PRICE

PART ~
PART_NUM, DESCRIPTION, ON_HAND, CLASS, WAREHOUSE, PRICE

CUSTOMER ~
CUSTOMER_NUM, CUSTOMER_NAME, STREET, CITY, STATE, ZIP, BALANCE, CREDIT_LIMIT, REP_NUM

ONLY LOOKING FOR DIRECTION NOT LOOKING FOR SOMEONE TO GIVE ME THE ANSWER. I NEED TO ANSWER ON MY OWN.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-10-16 : 00:58:04

quote:

ONLY LOOKING FOR DIRECTION NOT LOOKING FOR SOMEONE TO GIVE ME THE ANSWER. I NEED TO ANSWER ON MY OWN.

Feel good to hear this.

To start with:

You need to join all the four tables together in single query.

So take all the four tables in "FROM"

quote:

SELECT ORDER_NUM, ORDER_DATE
FROM ORDERS, CUSTOMER
WHERE CUSTOMER.CUSTOMER_NAME = 'Johnson Department Store'

In "WHERE" you need to join all the four table with the links they share.

Ex. ORDERS.ORDER_NUM = ORDER_LINE.ORDER_NUM and Orders table with customer & Order_line and Part_Num (I know you will figure out relationship).

Next step:
Use the same two where condition what you have used in posted query and also and one more where condition for Part_num.

Try it and let us know if you got the solution or got struck anywhere.

dlmagers10
Starting Member

48 Posts

Posted - 2010-10-16 : 01:13:14

In theory, not all three tables share a link. Orders [table] and Order_line [table] share ORDER_NUM but Customer [table] only has customer_num shared with Orders [table].

Am I thinking this through? You mentioned,

quote:
You need to join all the four tables together in single query.

Then you mentioned that I will need to do this?

quote:
In "WHERE" you need to join all the four table with the links they share.

Ex. ORDERS.ORDER_NUM = ORDER_LINE.ORDER_NUM and Orders table with customer & Order_line and Part_Num (I know you will figure out relationship).

I am seeing that I will need several select statements into one, right?

Just trying to figure this out.

Thank you.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-10-16 : 02:52:21

Based on the table description i feel that the link will be:

Orders.Order_num = Order_line.Order_num
Order_line.Part_num = part.part_num
Orders.customer_num = Customer.Customer_num

This way you are joining all the four tables.Just for your understanding;

Ex:
A is related to B
B is related to C

Then A is also related to C. We don't have /need any direct relationship with C. When we join A with B and then B with C, indirectly we are establishing relationship between A and C.

The same way when we join Orders with OrderLine and OrderLine with Parts we are establishing a relationship between Orders and Parts.
When we join Orders with customers, we are also establishing a relationship between Orderline and customers through Orders table.

Note:You only need single select statement and not multiple select joined with union.

Kristen
Test

22859 Posts

Posted - 2010-10-16 : 03:05:52

"looking for order_num and order_date"


SELECT order_num, order_date

"for each order"


FROM MyOrderTable AS O

"that was placed by Johnson Department Store"


JOIN MyCustomerTable AS C
      ON C.CustomerID = O.CustomerID
     AND C.CustomerName = 'Johnson Department Store'

"that also contains an order_line for a Gas Range (GAS RANGE PART_NUM IS 'DR93'."


WHERE EXISTS
(
    SELECT *
    FROM MyOrderLineTable AS OL
    WHERE OL.OrderID = O.OrderID
      AND OL.PART_NUM = 'DR93'
)

Kristen
Test

22859 Posts

Posted - 2010-10-16 : 03:10:24

You could use a JOIN for the OrderLine, but I wouldn't. There might be two Order Lines for 'DR93' on the same order - which would cause it to be selected twice.

You might, later, want to see all items on the order - that would make the query a bit more complex (two joins to the same OrderLine table - which is OK, just more complex).

But its pricipaly that the OrderLine might have more than one match (even if not in the situation, then in some others) that would cause me to use EXISTS rather than JOIN for OrderLine table. e.g. expand this to

"Customer bought 'DR93' AND 'DR94'"


WHERE EXISTS
(
    SELECT *
    FROM MyOrderLineTable AS OL
    WHERE OL.OrderID = O.OrderID
      AND OL.PART_NUM = 'DR93'
)
AND EXISTS
(
    SELECT *
    FROM MyOrderLineTable AS OL
    WHERE OL.OrderID = O.OrderID
      AND OL.PART_NUM = 'DR94'
)

dlmagers10
Starting Member

48 Posts

Posted - 2010-10-17 : 00:34:25

COULD IT BE......

SELECT ORDER_NUM, ORDER_DATE
FROM ORDERS
WHERE EXISTS
(SELECT *
FROM ORDER_LINE
WHERE ORDERS.ORDER_NUM = ORDER_LINE.ORDER_NUM
AND PART_NUM = 'DR93')
(SELECT *
FROM CUSTOMER
WHERE CUSTOMER.CUSTOMER_NUM = ORDERS.CUSTOMER_NUM
AND CUSTOMER_NUM ='608');

('608' IS THE CUSTOMER_NUM FOR JOHNSON DEPARTMENT STORE)

WHEN I MAKE MY SELECT STATEMENT IN MICROSOFT SQL SERVER SOFTWARE IT COMES UP AS AN ERROR STATING

quote:
Msg 4104, Level 16, State 1, Line 10
The multi-part identifier "ORDERS.CUSTOMER_NUM" could not be bound.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources