sql for repetitive records in n to n relation

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

SQL Server 2005 Forums

Transact-SQL (2005)

sql for repetitive records in n to n relation

Author

Topic

Mehdi
Yak Posting Veteran

64 Posts

Posted - 2008-09-08 : 06:41:09

Hi,
suppose i have this example
Supplier<S#,Sname..>
Part<P#,PNmae..>
SP<S#,P#,Count>

now i want to write the qurty returns me the suppliers which produced repettive products i.e if SP is

S#  P#  Count
1    1    3
2    1    2
1    1    1
2    2    2

it should retun the supplier 1 as it producec the product 1 twice
how to write its sql query?
thanks

_____________
Mehdi.
software student.

bjoerns
Posting Yak Master

154 Posts

Posted - 2008-09-08 : 06:51:45

[code]select S, P, count(*)
from SP
group by S, P
having count(*) > 1[/code]

Mehdi
Yak Posting Veteran

64 Posts

Posted - 2008-09-08 : 07:22:37

well,i wanted to write without having and group by

_____________
Mehdi.
software student.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-09-08 : 09:51:25

quote:
Originally posted by Mehdi

well,i wanted to write without having and group by

uh ... why? that's like asking someone how to get from Boston to England but insisting that you need to drive your car there.

- Jeff
http://weblogs.sqlteam.com/JeffS

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2008-09-08 : 16:10:28

quote:
Originally posted by Mehdi

well,i wanted to write without having and group by

No problem


SELECT *
FROM (
    SELECT DISTINCT S, P, COUNT(*) OVER (PARTITION BY S, P) AS ct
    FROM SP
  ) AS A
WHERE ct > 1

it's no distance at all to drive if you start off in the right Boston!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources