Selecting Distinct records - 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 2008 Forums

Transact-SQL (2008)

Selecting Distinct records

Author

Topic

mutlyp
Starting Member

20 Posts

Posted - 2012-05-03 : 17:11:01

I have a table that looks like this:
ItemID FacilityID
1 0
2 0
3 0
2 21
4 0
5 0
4 21
What I need the result to be is:
ItemID FacilityID
1 0
3 0
5 0

So I need to show all the Zeros in FacilityID that is not also marked as 21.

Is that possible?
thank you

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-03 : 17:15:11

[code]
SELECT ItemID,FieldID
FROM
(
SELECT *,SUM(CASE WHEN FacilityID = 21 THEN 1 ELSE 0 END) OVER (PARTITION BY ItemID) AS Occ
FROM Table
)t
WHERE Occ =0
[/code]

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

mutlyp
Starting Member

20 Posts

Posted - 2012-05-04 : 14:28:42

Thank you for the response and your query did work correctly. But now I have another problem. I have othe tables that need to be joined to this table. So the itemID is a Foriegh Key to another table that hold a list of Items also in that table there are Categories and Subcategroies for each item. When I try to incoroparate your query into my query that joins all the tables I get nothing but errors.
Can you please tell me what I am doing wrong.
Here is my query

SELECT dbo.jha_Map_Item_Facilities.iItemID,
dbo.jha_Map_Item_Facilities.iFacilityID, dbo.jha_Categories.sCat_Name,
dbo.jha_SubCategories.sSubCat_Name,
dbo.jha_ItemList.iIsActive
FROM
dbo.jha_Map_Item_Facilities INNER JOIN dbo.jha_ItemList ON dbo.jha_Map_Item_Facilities.iItemID = dbo.jha_ItemList.iItemID INNER JOIN dbo.jha_Categories ON dbo.jha_ItemList.iCatID = dbo.jha_Categories.iCatID INNER JOIN dbo.jha_SubCategories ON dbo.jha_ItemList.iSubCatID = dbo.jha_SubCategories.iSubCatID
WHERE
(dbo.jha_ItemList.iIsActive = 1) AND (dbo.jha_Map_Item_Facilities.iFacilityID = @FacID)

So what I need is to be able to incorurate your query with mine.
Please help thank you.

quote:
Originally posted by visakh16

SELECT ItemID,FieldID
FROM
(
SELECT *,SUM(CASE WHEN FacilityID = 21 THEN 1 ELSE 0 END) OVER (PARTITION BY ItemID) AS Occ
FROM Table
)t
WHERE Occ =0

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-04 : 18:38:21

just do like


SELECT *
FROM
(
SELECT dbo.jha_Map_Item_Facilities.iItemID,
dbo.jha_Map_Item_Facilities.iFacilityID, dbo.jha_Categories.sCat_Name, 
dbo.jha_SubCategories.sSubCat_Name,
dbo.jha_ItemList.iIsActive
FROM 
dbo.jha_Map_Item_Facilities INNER JOIN dbo.jha_ItemList ON dbo.jha_Map_Item_Facilities.iItemID = dbo.jha_ItemList.iItemID INNER JOIN dbo.jha_Categories ON dbo.jha_ItemList.iCatID = dbo.jha_Categories.iCatID INNER JOIN dbo.jha_SubCategories ON dbo.jha_ItemList.iSubCatID = dbo.jha_SubCategories.iSubCatID
WHERE 
(dbo.jha_ItemList.iIsActive = 1) AND (dbo.jha_Map_Item_Facilities.iFacilityID = @FacID)
)t
INNER JOIN (SELECT *,SUM(CASE WHEN FacilityID = 21 THEN 1 ELSE 0 END) OVER (PARTITION BY ItemID) AS Occ
FROM Table)t1
ON t1.ItemID = t.ItemID
AND t1.FacilityID = t.iFacilityID
AND t1.Occ=0

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

mutlyp
Starting Member

20 Posts

Posted - 2012-05-05 : 13:09:34

Hi Thank you again for your response. Unfortunately I am not getting any results when I run the query.
I noticed in your query at the end you have "FROM Table)t1" what is Table suppose to be. Because I get an error saying incorrect syntax next to Table.

I had to change my query a little and had to add a new table but the syntax is the same as yours.
Here is the query I came up with:

SELECT *
FROM
(
SELECT dbo.jha_Map_Task_Facilities.iTaskID, dbo.jha_Categories.sCat_Name, dbo.jha_SubCategories.sSubCat_Name, dbo.jha_Specifics.sSpec_Name,
dbo.jha_Map_Task_Facilities.iFacilityID, dbo.jha_TaskList.iIsActive
FROM
dbo.jha_TaskList INNER JOIN
dbo.jha_Categories ON dbo.jha_TaskList.iCatID = dbo.jha_Categories.iCatID INNER JOIN
dbo.jha_Map_Task_Facilities ON dbo.jha_TaskList.iTaskID = dbo.jha_Map_Task_Facilities.iTaskID LEFT OUTER JOIN
dbo.jha_SubCategories ON dbo.jha_TaskList.iSubCatID = dbo.jha_SubCategories.iSubCatID LEFT OUTER JOIN
dbo.jha_Specifics ON dbo.jha_TaskList.iSpecID = dbo.jha_Specifics.iSpecID
WHERE (dbo.jha_TaskList.iIsActive = 1) AND (dbo.jha_Map_Task_Facilities.iFacilityID = 21))t
INNER JOIN (SELECT *,SUM(CASE WHEN dbo.jha_Map_Task_Facilities.iFacilityID = 21 THEN 1 ELSE 0 END) OVER (PARTITION BY dbo.jha_Map_Task_Facilities.iTaskID) AS Occ FROM table)t1
ON t1.iTaskID = t.iTaskID
AND t1.iFacilityID = t.iFacilityID
AND t1.Occ=0

Please let me know what I am doing wrong.
Thanks again for the help it is very much apreciated.

quote:

Originally posted by visakh16

just do like


SELECT *
FROM
(
SELECT dbo.jha_Map_Item_Facilities.iItemID,
dbo.jha_Map_Item_Facilities.iFacilityID, dbo.jha_Categories.sCat_Name, 
dbo.jha_SubCategories.sSubCat_Name,
dbo.jha_ItemList.iIsActive
FROM 
dbo.jha_Map_Item_Facilities INNER JOIN dbo.jha_ItemList ON dbo.jha_Map_Item_Facilities.iItemID = dbo.jha_ItemList.iItemID INNER JOIN dbo.jha_Categories ON dbo.jha_ItemList.iCatID = dbo.jha_Categories.iCatID INNER JOIN dbo.jha_SubCategories ON dbo.jha_ItemList.iSubCatID = dbo.jha_SubCategories.iSubCatID
WHERE 
(dbo.jha_ItemList.iIsActive = 1) AND (dbo.jha_Map_Item_Facilities.iFacilityID = @FacID)
)t
INNER JOIN (SELECT *,SUM(CASE WHEN FacilityID = 21 THEN 1 ELSE 0 END) OVER (PARTITION BY ItemID) AS Occ
FROM Table)t1
ON t1.ItemID = t.ItemID
AND t1.FacilityID = t.iFacilityID
AND t1.Occ=0

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-05 : 13:40:24

quote:
Originally posted by mutlyp

Hi Thank you again for your response. Unfortunately I am not getting any results when I run the query.
I noticed in your query at the end you have "FROM Table)t1" what is Table suppose to be. Because I get an error saying incorrect syntax next to Table.

I had to change my query a little and had to add a new table but the syntax is the same as yours.
Here is the query I came up with:

SELECT *
FROM
(
SELECT dbo.jha_Map_Task_Facilities.iTaskID, dbo.jha_Categories.sCat_Name, dbo.jha_SubCategories.sSubCat_Name, dbo.jha_Specifics.sSpec_Name,
dbo.jha_Map_Task_Facilities.iFacilityID, dbo.jha_TaskList.iIsActive
FROM
dbo.jha_TaskList INNER JOIN
dbo.jha_Categories ON dbo.jha_TaskList.iCatID = dbo.jha_Categories.iCatID INNER JOIN
dbo.jha_Map_Task_Facilities ON dbo.jha_TaskList.iTaskID = dbo.jha_Map_Task_Facilities.iTaskID LEFT OUTER JOIN
dbo.jha_SubCategories ON dbo.jha_TaskList.iSubCatID = dbo.jha_SubCategories.iSubCatID LEFT OUTER JOIN
dbo.jha_Specifics ON dbo.jha_TaskList.iSpecID = dbo.jha_Specifics.iSpecID
WHERE (dbo.jha_TaskList.iIsActive = 1) AND (dbo.jha_Map_Task_Facilities.iFacilityID = 21))t
INNER JOIN (SELECT *,SUM(CASE WHEN dbo.jha_Map_Task_Facilities.iFacilityID = 21 THEN 1 ELSE 0 END) OVER (PARTITION BY dbo.jha_Map_Task_Facilities.iTaskID) AS Occ FROM ~~table~~ dbo.jha_Map_Task_Facilities)t1
ON t1.iTaskID = t.iTaskID
AND t1.iFacilityID = t.iFacilityID
AND t1.Occ=0

Please let me know what I am doing wrong.
Thanks again for the help it is very much apreciated.

quote:
Originally posted by visakh16

just do like

SELECT *
FROM
(
SELECT dbo.jha_Map_Item_Facilities.iItemID,
dbo.jha_Map_Item_Facilities.iFacilityID, dbo.jha_Categories.sCat_Name,
dbo.jha_SubCategories.sSubCat_Name,
dbo.jha_ItemList.iIsActive
FROM
dbo.jha_Map_Item_Facilities INNER JOIN dbo.jha_ItemList ON dbo.jha_Map_Item_Facilities.iItemID = dbo.jha_ItemList.iItemID INNER JOIN dbo.jha_Categories ON dbo.jha_ItemList.iCatID = dbo.jha_Categories.iCatID INNER JOIN dbo.jha_SubCategories ON dbo.jha_ItemList.iSubCatID = dbo.jha_SubCategories.iSubCatID
WHERE
(dbo.jha_ItemList.iIsActive = 1) AND (dbo.jha_Map_Item_Facilities.iFacilityID = @FacID)
)t
INNER JOIN (SELECT *,SUM(CASE WHEN FacilityID = 21 THEN 1 ELSE 0 END) OVER (PARTITION BY ItemID) AS Occ
FROM Table)t1
ON t1.ItemID = t.ItemID
AND t1.FacilityID = t.iFacilityID
AND t1.Occ=0

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

use actual table name in query

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

mutlyp
Starting Member

20 Posts

Posted - 2012-05-05 : 21:55:58

Hi Again,
Sorry for not getting this but I don't understand what you mean by "use actual table name in query"
I used "jha_Map_Task_Facilities" which is the "Main" table I guess but when ran I get an error saying incorrct syntak next to t1.
Like I said I have to change things around a little here is my code:
SELECT *
FROM
(
SELECT dbo.jha_Map_Task_Facilities.iTaskID,
dbo.jha_Map_Task_Facilities.iFacilityID, dbo.jha_Categories.sCat_Name,
dbo.jha_SubCategories.sSubCat_Name,
dbo.jha_TaskList.iIsActive
FROM
dbo.jha_Map_Task_Facilities INNER JOIN dbo.jha_TaskList ON dbo.jha_Map_Task_Facilities.iTaskID = dbo.jha_TaskList.iTaskID INNER JOIN dbo.jha_Categories ON dbo.jha_TaskList.iCatID = dbo.jha_Categories.iCatID INNER JOIN dbo.jha_SubCategories ON dbo.jha_TaskList.iSubCatID = dbo.jha_SubCategories.iSubCatID
WHERE
(dbo.jha_TaskList.iIsActive = 1) AND (dbo.jha_Map_Task_Facilities.iFacilityID = @FacID)
)t
INNER JOIN (SELECT *,SUM(CASE WHEN FacilityID = 21 THEN 1 ELSE 0 END) OVER (PARTITION BY TaskID) AS Occ
FROM dbo.jha_Map_Task_Facilities)t1
ON t1.TaskID = t.iTaskID
AND t1.FacilityID = t.iFacilityID
AND t1.Occ=0

again sorry for being a pain but please let me know what I am doing wrong.
Thank you

quote:
Originally posted by visakh16

quote:
Originally posted by mutlyp

Hi Thank you again for your response. Unfortunately I am not getting any results when I run the query.
I noticed in your query at the end you have "FROM Table)t1" what is Table suppose to be. Because I get an error saying incorrect syntax next to Table.

I had to change my query a little and had to add a new table but the syntax is the same as yours.
Here is the query I came up with:

SELECT *
FROM
(
SELECT dbo.jha_Map_Task_Facilities.iTaskID, dbo.jha_Categories.sCat_Name, dbo.jha_SubCategories.sSubCat_Name, dbo.jha_Specifics.sSpec_Name,
dbo.jha_Map_Task_Facilities.iFacilityID, dbo.jha_TaskList.iIsActive
FROM
dbo.jha_TaskList INNER JOIN
dbo.jha_Categories ON dbo.jha_TaskList.iCatID = dbo.jha_Categories.iCatID INNER JOIN
dbo.jha_Map_Task_Facilities ON dbo.jha_TaskList.iTaskID = dbo.jha_Map_Task_Facilities.iTaskID LEFT OUTER JOIN
dbo.jha_SubCategories ON dbo.jha_TaskList.iSubCatID = dbo.jha_SubCategories.iSubCatID LEFT OUTER JOIN
dbo.jha_Specifics ON dbo.jha_TaskList.iSpecID = dbo.jha_Specifics.iSpecID
WHERE (dbo.jha_TaskList.iIsActive = 1) AND (dbo.jha_Map_Task_Facilities.iFacilityID = 21))t
INNER JOIN (SELECT *,SUM(CASE WHEN dbo.jha_Map_Task_Facilities.iFacilityID = 21 THEN 1 ELSE 0 END) OVER (PARTITION BY dbo.jha_Map_Task_Facilities.iTaskID) AS Occ FROM ~~table~~ dbo.jha_Map_Task_Facilities)t1
ON t1.iTaskID = t.iTaskID
AND t1.iFacilityID = t.iFacilityID
AND t1.Occ=0

Please let me know what I am doing wrong.
Thanks again for the help it is very much apreciated.

quote:
Originally posted by visakh16

just do like

SELECT *
FROM
(
SELECT dbo.jha_Map_Item_Facilities.iItemID,
dbo.jha_Map_Item_Facilities.iFacilityID, dbo.jha_Categories.sCat_Name,
dbo.jha_SubCategories.sSubCat_Name,
dbo.jha_ItemList.iIsActive
FROM
dbo.jha_Map_Item_Facilities INNER JOIN dbo.jha_ItemList ON dbo.jha_Map_Item_Facilities.iItemID = dbo.jha_ItemList.iItemID INNER JOIN dbo.jha_Categories ON dbo.jha_ItemList.iCatID = dbo.jha_Categories.iCatID INNER JOIN dbo.jha_SubCategories ON dbo.jha_ItemList.iSubCatID = dbo.jha_SubCategories.iSubCatID
WHERE
(dbo.jha_ItemList.iIsActive = 1) AND (dbo.jha_Map_Item_Facilities.iFacilityID = @FacID)
)t
INNER JOIN (SELECT *,SUM(CASE WHEN FacilityID = 21 THEN 1 ELSE 0 END) OVER (PARTITION BY ItemID) AS Occ
FROM Table)t1
ON t1.ItemID = t.ItemID
AND t1.FacilityID = t.iFacilityID
AND t1.Occ=0

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

use actual table name in query

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-05 : 23:41:28

Sorry I'm not getting any error. Are you using SQL 2005 and above?

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

mutlyp
Starting Member

20 Posts

Posted - 2012-05-06 : 15:13:04

Yes I am using 2008 R2

quote:
Originally posted by visakh16

Sorry I'm not getting any error. Are you using SQL 2005 and above?

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

mutlyp
Starting Member

20 Posts

Posted - 2012-05-06 : 15:30:56

quote:
Originally posted by visakh16

Sorry I'm not getting any error. Are you using SQL 2005 and above?

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

mutlyp
Starting Member

20 Posts

Posted - 2012-05-06 : 19:01:34

I figured it out. Had to get rid of the @FacID. That narrows the field down so I get nothing back.
Here is the code that worked:
Select *
FROM
(
SELECT dbo.jha_Map_Task_Facilities.iTaskID, dbo.jha_Map_Task_Facilities.iFacilityID, dbo.jha_Categories.sCat_Name, dbo.jha_SubCategories.sSubCat_Name,
dbo.jha_Specifics.sSpec_Name
FROM dbo.jha_Categories INNER JOIN
dbo.jha_TaskList ON dbo.jha_Categories.iCatID = dbo.jha_TaskList.iCatID INNER JOIN
dbo.jha_Map_Task_Facilities ON dbo.jha_TaskList.iTaskID = dbo.jha_Map_Task_Facilities.iTaskID LEFT OUTER JOIN
dbo.jha_SubCategories ON dbo.jha_TaskList.iSubCatID = dbo.jha_SubCategories.iSubCatID LEFT OUTER JOIN
dbo.jha_Specifics ON dbo.jha_TaskList.iSpecID = dbo.jha_Specifics.iSpecID
WHERE (dbo.jha_TaskList.iIsActive = 1))t
INNER JOIN (SELECT dbo.jha_Map_Task_Facilities.iTaskID, dbo.jha_Map_Task_Facilities.iFacilityID, SUM(CASE WHEN dbo.jha_Map_Task_Facilities.iFacilityID = 21 THEN 1 ELSE 0 END) OVER (PARTITION BY dbo.jha_Map_Task_Facilities.iTaskID) AS Occ
FROM dbo.jha_Map_Task_Facilities)t1
ON t1.iTaskID = t.iTaskID AND t1.iFacilityID = t.iFacilityID AND t1.Occ=0

Thanks again for all your help

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-06 : 19:24:59

unless you post some data and explain what you're trying to get i dont think anybody will be able to help you out

post resulst of first query and then explain what you want out of it

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources