Please help with a query! - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Please help with a query!

Author

Topic

rusmo
Starting Member

2 Posts

Posted - 2005-08-17 : 14:07:47

I have a customer table with two VARCHAR columns:
customerId (primary key)
hardwareId

It is possible for more than one customer to have the same hardwareId. I need to know how to query this table to return a list of customer records (rows) that have non-unique hardwareId's. To be clear, here is a sample table and the sample results I wish to get back from the query:

SAMPLE TABLE (customerID, hardwareID):
0, A
1, B
2, B
3, C
4, D
5, D

DESIRED RESULTS (customerID, hardwareID):
1, B
2, B
4, D
5, D

Thanks in advance for the help!

X002548
Not Just a Number

15586 Posts

Posted - 2005-08-17 : 14:12:18

Well what you say you want is not what your result is...so to get your result set

SELECT * FROM myTable99 WHERE harwareID IN
(SELECT HarwareID FROM myTable99 GROUP BY harwareID HAVING COUNT(*) > 1)

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

rusmo
Starting Member

2 Posts

Posted - 2005-08-17 : 15:13:33

Thanks! The 'HAVING COUNT' part was where I was fuzzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources