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 |
|
racoonblobby
Starting Member
1 Post |
 Posted - 2006-04-03 : 15:01:44
|
Hi folks  Been banging my head on this one, as in theory this should work  , but it does not show 'only suppliers who supply more than one item - then show items')select * from Products where Exists(Select SupplierID, count(SupplierID)from Products group by SupplierIDhaving count(SupplierID)>1)... other conditions later;This query will get alot more complicated once I get this bit to work, and I am using the whereExist way as I will use this to search through 20000 fields first to see multiple entries for an entity (see below).In this example I am trying to return all fields in a table where there is a duplicate in the field SupplierID - ie supplier supplies more than one item. This runs on the Northwind database. I am trying to write a query for a work related issue and this is an almost identical problem.When I change >1 to >4 no rows are returned, which makes me think that the count and having function is working in some way - just not as expected.Any help and ideas most welcome,Best wishes,RB |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-04-03 : 19:20:00
|
[code]select *from Products pwhere Exists( Select SupplierID, count(SupplierID) from Products x where x.SupplierID = p.SupplierID group by SupplierID having count(SupplierID) > 1)[/code]
KHChoice is an illusion, created between those with power, and those without. |
 |
|
|
|
|
|
Duplicate fields- have I missed something obvious?