Checking if - 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)

Checking if

Author

Topic

John Sourcer
Yak Posting Veteran

91 Posts

Posted - 2008-10-27 : 08:11:28

Hi All,

Blind as a bat here! :(

I have 3 tables:

Sets
SetID
1
2

SetsProperties
ID | SetID | PropertyID
1 1 2
2 1 3

Properties
ID
1
2
3

If I am passed PropertyID = (2,3) how can I check whether a set exists with just these properties. i.e. If I am passed PropertyID = (2,3,4). I want to create a new set!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-27 : 08:20:52

can you show sample output format please?

John Sourcer
Yak Posting Veteran

91 Posts

Posted - 2008-10-27 : 08:25:13

Thanks visakh16,

I was trying this:

SELECT DISTINCT Sets.SetID FROM Sets INNER JOIN SetsProperties ON Sets.SetID = SetsProperties.SetID WHERE SetsProperties.PropertyID IN (2,3)

But I want the SetID where SetsProperties.PropertyID = 2 and SetsProperties.PropertyID = 3 but not where SetsProperties.PropertyID = 4

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-10-27 : 13:27:55

Are you looking for SetIDs that have only properties 2 and 3 and only 2 and 3. Meaning not setIDs with just 2 or SetIDs with 2,3,4 etc?

If so, you could try something like this (I populated a Temp table (variable) to join to):

DECLARE @Sets TABLE (SetID INT)
INSERT @Sets
SELECT 1
UNION ALL SELECT 2
UNION ALL SELECT 3

DECLARE @SetsProperties TABLE (ID INT, SetID INT, PropertyID INT)
INSERT @SetsProperties
SELECT 1, 1, 2
UNION ALL SELECT 2, 1, 3
UNION ALL SELECT 3, 2, 2
UNION ALL SELECT 4, 2, 3
UNION ALL SELECT 5, 2, 4
UNION ALL SELECT 6, 3, 2

DECLARE @Temp TABLE (PropertyID INT)
INSERT @Temp
SELECT 2
UNION ALL SELECT 3


SELECT
	S.SetID
FROM
	@SetsProperties AS S
INNER JOIN
	(
		SELECT A.SetID
		FROM @SetsProperties AS A
		INNER JOIN @Temp AS B
		ON A.PropertyID = B.PropertyID
		GROUP BY A.SetID
		HAVING COUNT(*) = (SELECT COUNT(*) FROM @Temp)
	) AS T
	ON S.SetID = T.SetID
GROUP BY 
	S.SetID
HAVING 
	COUNT(*) = (SELECT COUNT(*) FROM @Temp)

Also, you don't need the ID on SetsProperties as the SetID and PropertyID should be unique (f I understand your schema).

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-27 : 13:37:12

quote:
Originally posted by John Sourcer

Thanks visakh16,

I was trying this:

SELECT DISTINCT Sets.SetID FROM Sets INNER JOIN SetsProperties ON Sets.SetID = SetsProperties.SetID WHERE SetsProperties.PropertyID IN (2,3)

But I want the SetID where SetsProperties.PropertyID = 2 and SetsProperties.PropertyID = 3 but not where SetsProperties.PropertyID = 4

SELECT s.*
FROM Sets s
INNER JOIN(SELECT SetID
           FROM SetsProperties
           WHERE PropertyID IN (2,3,4)
           GROUP BY SetID
           HAVING COUNT(DISTINCT PropertyID)=2
           AND SUM(CASE WHEN PropertyID =4 THEN 1 ELSE 0 END)=0
           )p
ON p.SetID=s.SetID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources