SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Database Design and Application Architecture
 I am making head way using IN operator
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

dlmagers10
Starting Member

48 Posts

Posted - 10/12/2010 :  00:48:27  Show Profile  Reply with Quote
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  Show Profile  Reply with Quote
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.


Go to Top of Page

GilaMonster
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 10/12/2010 :  02:05:11  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000