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.
| Author |
Topic |
|
dlmagers10
Starting Member
48 Posts |
 Posted - 2010-10-12 : 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 - 2010-10-12 : 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
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-10-12 : 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. egselect StudentName from Students WHERE Students.StudentID IN (SELECT ExamScores.StudentID FROM ExamScores WHERE Exam='Physics' and Score > 75)--Gail ShawSQL Server MVP |
|
|
|
|
|
|
|
|
I am making head way using IN operator