Regarding Join - SQL Server Forums

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

Regarding Join

Author

Topic

kashyap_sql
Posting Yak Master

174 Posts

Posted - 2010-10-25 : 02:05:55

I am trying for this
get customer name and employee pairs such that the customer with name has placed an order through the employee the customer should make at-least two orders i,e >1

Employees table is


ENO ENAME		ZIP		HDATE
1   xsss		111		2008-03-17 00:00:00.000
2   lkjdhg		222		2009-10-19 00:00:00.000
3   dsaddfff            333		2008-02-19 00:00:00.000

Customers Table is


CNO		CNAME	Street	ZIP		PHONE
2201	PGB		NSDSTE 188209	10074321
2202	NGO		NHDTE  12209	74321
2203	WSQ		ASATE  13509	40074321

orders table is


ONO	CNO    ENO	RD.date	                 SHP.DATE
1001	2200	1	2008-02-19 00:00:00.000	2009-03-10 00:00:00.000
1002	2201	2	2008-03-29 00:00:00.000	2009-01-11 00:00:00.000
1003	2200	3	2008-09-19 00:00:00.000	2009-06-02 00:00:00.000

i am trying like


select C.CNAME, E.Ename from customers C join Employees E ON O.CNO=C.CNO and O.PNO=E.PNO or
(select CNO from orders o group by CNO having count(ENO)>1)

can you help me for the modification on code

With Regards
Kashyap M

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-10-25 : 03:14:37

Are you looking for this:

select C.CNAME, E.Ename from customers C inner join Orders O on C.CNO = O.Cno
inner join Employees E ON O.ENO=E.ENO
Group by C.CName,E.Ename,O.ONO
Having count(*) >1

Note: I have not carried out any testing.

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2010-10-25 : 04:58:23

Bokra, i think u need to remove O.ONO from group by

"There is only one difference between a dream and an aim.
A dream requires soundless sleep to see,
whereas an aim requires sleepless efforts to achieve..!!"

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-10-25 : 05:36:45

quote:
Originally posted by PeterNeo

Bokra, i think u need to remove O.ONO from group by

"There is only one difference between a dream and an aim.
A dream requires soundless sleep to see,
whereas an aim requires sleepless efforts to achieve..!!"

Hi peter,
You are right. I misunderstood the requirement. My understanding was that the OP wants to return the details of employee and customer having multiple order lines (i didn't observe the order table).

Thanks for correction.

kashyap_sql
Posting Yak Master

174 Posts

Posted - 2010-10-25 : 06:27:07

Thanks bohra and peterneo for your consideration thanks alot

With Regards
Kashyap M

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-10-25 : 06:53:18

quote:
Originally posted by kashyap_sql

Thanks bohra and peterneo for your consideration thanks alot

With Regards
Kashyap M

You are welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources