Query Help eliminating duplicate records

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 2008 Forums

Transact-SQL (2008)

Query Help eliminating duplicate records

Author

Topic

mutlyp
Starting Member

20 Posts

Posted - 2012-05-03 : 12:58:37

I have a table that holds ItemIds and LocationIDs. It's a many to many table because multiple locations can use the same Item. But there are also "Default" items that are offered to the user. So they can choose a "Deault" item or add a custom item o thier loaction. So I marked all the "Default" items in the table with a Zero for the LocationID. Beacuse there is no location with a Zero for the ID.
If a user decides to use one of the "Defualt" items the ItemID for that item is added to the table with their LocationID for that particular Location. So the table would look like this:
ItemID LocationID
1 0
2 0
3 0
4 0
5 0
2 21
3 21
So this shows that Location 21 has chosen item 2 and 3 to be used at thier location (21).
But now I need a query that will return all the LocationID of 21 and all the "Default" items that have not been chosen by location 21.
So the results should look like this:
ItemID LocaionID
1 0
2 21
3 21
4 0
5 0

Hope this makes since. Please help it's driving me crazy.
Thank you

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-05-03 : 13:21:21

Assuming you have no locations with a negative number for the location id, you can do the following:

SELECT
	ItemId,
	MAX(LocationId)
FROM
	YourTable
GROUP BY
	ItemId
ORDER BY
	ItemId;

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-03 : 14:54:25

quote:
Originally posted by sunitabeck

Assuming you have no locations with a negative number for the location id, you can do the following:
SELECT
ItemId,
MAX(LocationId)
FROM
YourTable
WHERE LocationId=@LocationID
GROUP BY
ItemId
ORDER BY
ItemId;

If you've records for locationids other than 21 also present
You should pass values for @LocationID based on your requirement

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mutlyp
Starting Member

20 Posts

Posted - 2012-05-03 : 15:20:32

Thank you for the response. Unfortuately this is not working. If I don't add a where clause I get back all the results. But as soon as I add the where clause for the locationid it only brings back the ones for that particular location id. It does not bring back any of the defaults that were not used by the location Id.
Please help
Thank you

quote:
Originally posted by visakh16

quote:
Originally posted by sunitabeck

Assuming you have no locations with a negative number for the location id, you can do the following:
SELECT
ItemId,
MAX(LocationId)
FROM
YourTable
WHERE LocationId=@LocationID
GROUP BY
ItemId
ORDER BY
ItemId;

If you've records for locationids other than 21 also present
You should pass values for @LocationID based on your requirement

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-03 : 15:33:35

ah sorry . it should be this


SELECT ItemID,
MAX(LocationID)
FROM table 
WHERE t.LocationID IN (0,@LocationID)
GROUP BY ItemID

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mutlyp
Starting Member

20 Posts

Posted - 2012-05-03 : 17:12:05

Thank you that worked.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-03 : 17:12:58

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources