SQL 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 2005 Forums

Transact-SQL (2005)

SQL query

Author

Topic

sqlpal2009
Yak Posting Veteran

58 Posts

Posted - 2009-01-20 : 08:57:43

Hello All,

I how to write a query to find out more than one record for the same component. Here is my sample data

componentid locationid
200001 005
200002 001
200003 001
200003 003
200004 005
200005 001
200006 005
200006 004
200006 001

I have to show only those records where the locatonid is different for the same componentid. Result:
200003 001
200003 003
200006 005
200006 004
200006 001

Thanks,
-S

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-20 : 09:01:51

[code]
SELECT t.componentid,t.locationid
FROM Table t
INNER JOIN(SELECT componentid
FROM Table
GROUP BY componentid
HAVING COUNT(DISTINCT locationid)>1
)tmp
ON tmp.componentid=t.componentid
[/code]

sqlpal2009
Yak Posting Veteran

58 Posts

Posted - 2009-01-20 : 09:18:01

Thanks visakh16.

Those records also appeared where the componentid is same and the locationid is same and has count > 1.
200011 004
200011 004
200011 004

I want only those records where the count > 1 for the different locations of the same component.

Thanks,
-S

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-20 : 09:23:32

quote:
Originally posted by sqlpal2009

Thanks visakh16.

Those records also appeared where the componentid is same and the locationid is same and has count > 1.
200011 004
200011 004
200011 004

I want only those records where the count > 1 for the different locations of the same component.

Thanks,
-S

nope.it wont list them unless they have atleast one locationid which is different


SELECT t.componentid,t.locationid
FROM Table t
INNER JOIN(SELECT componentid 
FROM Table
GROUP BY componentid
HAVING COUNT(DISTINCT locationid)>1
)tmp
ON tmp.componentid=t.componentid

sqlpal2009
Yak Posting Veteran

58 Posts

Posted - 2009-01-20 : 09:50:32

You are correct!

There is a where clause which I had to add in the query but I had to add teh same where clause to the sub query.

Thanks,
-S

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-20 : 11:36:19

quote:
Originally posted by sqlpal2009

You are correct!

There is a where clause which I had to add in the query but I had to add teh same where clause to the sub query.

Thanks,
-S

welcome

sqlpal2009
Yak Posting Veteran

58 Posts

Posted - 2009-01-21 : 09:34:08

Additionally, how do I get the first record if there are more records with the different location for the same component id. It does not matter which location I get but the result has to show the first location. If the location is NULL/blank then the second record.

Thanks in advance
-S

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-21 : 09:38:20


SELECT t.componentid,MAX(t.locationid)
FROM Table t
INNER JOIN(SELECT componentid 
FROM Table
GROUP BY componentid
HAVING COUNT(DISTINCT locationid)>1
)tmp
ON tmp.componentid=t.componentid
GROUP BY t.componentid

sqlpal2009
Yak Posting Veteran

58 Posts

Posted - 2009-01-21 : 10:58:03

Thank you visakh16.

Now I have to display the sum of revenues for each location by quarter. I am working on that. If you remember you give you the solution yesterday for the results by quarters.

Thanks again,
-S

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-21 : 11:16:25

welcome

let me know if you still face any issues

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources