Help: fetching rwos with 2 tables

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

Help: fetching rwos with 2 tables

Author

Topic

arunvpy
Starting Member

3 Posts

Posted - 2010-05-28 : 06:31:30

Hi,
Please guide for the below scenario.

I have 2 tables called customer and order both with primary key cust_id and order_id respectively.One cust_id may have more than one order in order table. my requirement is to "find the customer whose all order is on 1-Jan-2010"
Please help me as I am new to SQL
Thanks,
Arun P V
e-mail id:arunvpy@gmail.com

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2010-05-28 : 06:51:02

can u post ur table structure and sample output and required output

arunvpy
Starting Member

3 Posts

Posted - 2010-05-28 : 07:07:02

Customer
--------
Cust_Id Cust_name

1 n1
2 n2
3 n3

Order
-----
Order_Id Cust_Id Order_Date

1 1 1-Jan-2010
2 1 1-Jan-2010
3 1 1-Jan-2010
4 2 11-Jan-2010

output required
----------------

Cust_name

n1

if Customer table is like below,
Order
-----
Order_Id Cust_Id Order_Date

1 1 1-Jan-2010
2 1 1-Jan-2010
3 1 15-Jan-2010
4 2 11-Jan-2010

output required :

Cust_name
--nothing should come--

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2010-05-28 : 07:15:34

declare @date datetime

select @date = '1/1/2010'

select Cust_name from customer c
inner join (select cust_id, count(order_id)as ocnt from order) oc on oc.cust_id = c.cust_id
inner join (select cust_id, count(order_id)cnt from order where order_date = @date ) o on o.cust_id = c.cust_id
where o.cnt = oc.cnt

arunvpy
Starting Member

3 Posts

Posted - 2010-05-28 : 07:27:13

Thank you very much !

Arun

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2010-05-28 : 08:24:18

quote:
Originally posted by arunvpy

Thank you very much !

Arun

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources