November 22, 2011 at 1:35 pm
There are a few ways to skin this cat, but here are two ....
DECLARE @a TABLE
(CriteriaKoppelID INT, fkObjectID INT, fkCriteriaID INT)
INSERT @a SELECT 1,1,17
INSERT @a SELECT 2,1,18
INSERT @a SELECT 3,2,17
INSERT @a SELECT 4,3,18
INSERT @a SELECT 5,4,16
INSERT @a SELECT 6,4,15
SELECT *
FROM @a
----
SELECT DISTINCT a.fkObjectID
FROM @a AS a
INNER JOIN @a AS b
ON a.fkObjectID = b.fkObjectID
AND b.fkCriteriaID = 17
INNER JOIN @a AS c
ON a.fkObjectID = c.fkObjectID
AND c.fkCriteriaID = 18
----
SELECT DISTINCT fkObjectID
FROM @a AS a
WHERE
EXISTS (SELECT fkObjectID FROM @a
WHERE fkObjectID = a.fkObjectID
AND fkCriteriaID = 17)
AND
EXISTS (SELECT fkObjectID FROM @a
WHERE fkObjectID = a.fkObjectID
AND fkCriteriaID = 18)
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgNovember 22, 2011 at 1:54 pm
Jason,
Thanks! Both ways produce a skinned cat indeed!
Now, in combination with a piece of vb code in my application, what to do if the fkCriteriaID is dynamic? Can i build a stored procedure that takes a comma seprated Parameter, or should i build the query dynamicly in code?
November 22, 2011 at 2:02 pm
Are you going to ALWAYS have two values?
Yes = Procedure
No = depends 😀 but probably a procedure of some type.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgNovember 22, 2011 at 2:15 pm
Hi Jason,
No it can differ. Sometimes not even an 'selection' at all.
it's a selection of Vacationhomes. (has Bath, has toilet :-P, is petfree, etcetera..)
I`m now coding a small piece of code that builds a query dynamically in Vb.net, as i`m a coder, not an sql-er.. 🙂
Viewing 4 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply