| Author |
 Topic  |
|
|
dlmagers10
Starting Member
48 Posts |
 Posted - 10/12/2010 : 00:48:27
|
Ok, I have a question (problem) I am trying to using the IN operator to find the number and name of each customer that placed an order on October 23, 2010.
Tables ~
rep r has rep_num, last_name, first_name, street, city, state, zip, commission, rate.
customer c has customer_num, customer_name, street, city, state, zip, balance, credit_limit, rep_num.
orders o has order_num, order_date, customer_num.
order_line l has order_num, part_num, num_ordered, quoted_price.
part p has part_num, description, on_hand, class, warehouse, price.
So far I have:
SELECT c.CUSTOMER_NUM, c.CUSTOMER_NAME
FROM CUSTOMER C, orders o
WHERE ORDER_DATE IN
(SELECT CUSTOMER_NUM
FROM CUSTOMER
WHERE CUSTOMER_NUM = 10/23/2010);
In the results it is just showing me the customer_num and customer_name.
ANY SUGGESTIONS? |
|
|
dlmagers10
Starting Member
48 Posts |
Posted - 10/12/2010 : 00:54:45
|
Actually it should be as follows:
SELECT c.CUSTOMER_NUM, c.CUSTOMER_NAME
FROM CUSTOMER C, orders o
WHERE ORDER_DATE IN
(SELECT o.order_date
FROM orders o
WHERE o.order_date = 10/23/2010);
still just getting the column heads: customer_num, customer_name.
 |
 |
|
|
GilaMonster
Flowing Fount of Yak Knowledge
South Africa
4507 Posts |
Posted - 10/12/2010 : 02:05:11
|
Why are you using IN at all here? Simple join between the two tables, I assume on CustomerID and a simple filter on order date.
In is when you want all rows of one table that match rows of another table. Typically the IN is used on a foriegn-key relationship. eg
select StudentName from Students WHERE Students.StudentID IN (SELECT ExamScores.StudentID FROM ExamScores WHERE Exam='Physics' and Score > 75)
--
Gail Shaw
SQL Server MVP |
|
|
| |
Topic |
|
|
|
I am making head way using IN operator
