November 22, 2011 at 1:23 pm
Hi there,
i have a table: tblcriteriakoppel, containing fields CriteriaKoppelID, fkObjectID, fkCriteriaID.
Now i want to select (and group) fkObjectID's from where the fkCriteriaID are (example) 17 AND 18.
table content like this:
CriteriaKoppelID, fkObjectID, fkCriteriaID
1 1 17
2 1 18
3 2 17
4 3 18
Say is want to return the fkObjectID's where the criteriaID are both 17 and 18. I can see fkObjectID 1 does, but how do i filter this?
an OR gives me objectID 1, 2 and 3, and an AND gives me nothing.
Seems so simple, but cannot get it done. Have tried functions, IN, strange WHERE statements, exotic JOIN's ...
Who can give me a hint?
Thanks in advance.
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 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply