What can I use in stead of the IN operator (to ''AND'' the conditions, not ''OR'')
-
The IN oparator looks at value1 OR value2 OR value3 etc.
What I want is the same, but with value1 AND value2 AND value3 etc.
Is this possible, and how to do this (with the ANY or ALL operator?)
E.G.
I have some houses to let. But some want no dogs, are at the waterside, etc.
I want all the conditions to be true,
not just one of them as the IN operator does.
Here is an example for what I want
DECLARE @Obs TABLE(ObID INT, ObName VARCHAR(10))
INSERT @Obs SELECT 1, 'house1'
INSERT @Obs SELECT 2, 'house2'
INSERT @Obs SELECT 3, 'house3'
INSERT @Obs SELECT 4, 'house4'
INSERT @Obs SELECT 5, 'house5'
DECLARE @Prefs TABLE(PrefID INT, PrefName VARCHAR(10))
INSERT @Prefs SELECT 1, 'nosmoke'
INSERT @Prefs SELECT 2, 'nokids'
INSERT @Prefs SELECT 3, 'airco'
INSERT @Prefs SELECT 4, 'nodogs'
INSERT @Prefs SELECT 5, 'waterside'
DECLARE @ObPrefs TABLE(ObID INT, PrefID INT)
INSERT @ObPrefs SELECT 1,1
INSERT @ObPrefs SELECT 1,2
INSERT @ObPrefs SELECT 1,5
INSERT @ObPrefs SELECT 2,1
INSERT @ObPrefs SELECT 2,2
INSERT @ObPrefs SELECT 2,4
INSERT @ObPrefs SELECT 2,5
INSERT @ObPrefs SELECT 3,1
-- SELECT PrefID FROM @Prefs WHERE PrefName IN ('nodogs', 'waterside')-> (4,5)
SELECT * FROM @Obs O WHERE EXISTS
(SELECT NULL FROM @ObPrefs
WHERE ObID = O.ObID AND PrefID IN
(SELECT PrefID FROM @Prefs WHERE PrefName IN ('nodogs', 'waterside') ) )
This gives me house1 and house2, not the answer I want.
I want only house2 as this house satisfies both 'nodogs' and 'waterside'
What should the sql be to match this condition?
and what happens when a empty condition is given (as I want all the houses)?
-
SELECT *
FROM @Obs O
WHERE EXISTS
(SELECT 1
FROM @ObPrefs A
INNER JOIN @PREFS B
ON A.PREFID = B.PREFID
WHERE A.ObID = O.ObID
AND B.PrefName IN ('nodogs', 'waterside')
GROUP BY A.OBID
HAVING COUNT(1) = 2)
--OUTPUT
ObID ObName
----------- ----------
2 house2
-
Not sure this is the most elegant solution, but it does seem to fulfill the requirement to return only the house that complies to all elements in the IN clause.
SELECT o.obId, o.obName
FROM @Obs O
join @ObPrefs op
on o.obid = op.obid
join @Prefs p
on op.prefid = p.prefid
where p.prefname IN ('nodogs', 'waterside')
group by o.obId,
o.obName
having count(*) = (select count(*) from @prefs where prefname IN ('nodogs', 'waterside') )
Perhaps for the case with no preferences given (ie return all houses) it'd be easiest to check on that, and then just do a straight select without filter..?
/Kenneth
-
srama and kenneth, thank you.
Both solutions work perfect. They give the right answers, here in the demo, and also in my "real life" application.
But, these solutions are beyond my understanding.
Can you give me a hint on the way your solution is functioning?
-
It's basically the 'second step' of what you already had - a list of houses where any preference was matched.
The 'base query' gave you:
SELECT o.obId, o.obName
FROM @Obs O
join @ObPrefs op
on o.obid = op.obid
join @Prefs p
on op.prefid = p.prefid
where p.prefname IN ('nodogs', 'waterside')
obId obName
----------- ----------
1 house1
2 house2
2 house2
... which is more than needed... so we need some kind of filter, and since we also know that if there are more than one preference given, we would also get duplicate rows, so we need to compact those as well.
A GROUP BY is perfect for removing duplicates.
SELECT o.obId, o.obName
FROM @Obs O
join @ObPrefs op
on o.obid = op.obid
join @Prefs p
on op.prefid = p.prefid
where p.prefname IN ('nodogs', 'waterside')
group by o.obId,
o.obName
obId obName
----------- ----------
1 house1
2 house2
Ah, but.. darn... we got rid of the dupe, but still have the partial match.. What to do..?
Well, as defined, a 'match' is any combo of obId + obName that has the same number of rows as number of preferences given in the IN clause. So... we filter out all obId + obName in the GROUP BY that has a different number of rows than, in this case, two.
Remeber, 'base result' looked like this:
obId obName
----------- ----------
1 house1
2 house2
2 house2
So, we use HAVING to filter out the GROUP BY
SELECT o.obId, o.obName
FROM @Obs O
join @ObPrefs op
on o.obid = op.obid
join @Prefs p
on op.prefid = p.prefid
where p.prefname IN ('nodogs', 'waterside')
group by o.obId,
o.obName
having count(*) = 2
obId obName
----------- ----------
2 house2
Lastly, since we don't want to hardcode it to only deal with two preferences, we find out how many preferences are given by counting the number of rows returned from the prefs table with the same IN clause.
SELECT o.obId, o.obName
FROM @Obs O
join @ObPrefs op
on o.obid = op.obid
join @Prefs p
on op.prefid = p.prefid
where p.prefname IN ('nodogs', 'waterside')
group by o.obId,
o.obName
having count(*) = (select count(*) from @prefs where prefname IN ('nodogs', 'waterside') )
..and that's about it
/Kenneth
-
The reason they work is because they are using a grouping function, COUNT(*) and comparing the number of matches with the total number of options that have to be matched. For instance, the last one:
FROM @Obs O join @ObPrefs op on o.obid = op.obid join @Prefs p on op.prefid = p.prefid
You have inner joins across 3 tables. Therefore, only when we have matches based on the ON clauses will we get a row back.
where p.prefname IN ('nodogs', 'waterside')Here we specify what prefnames are acceptable. Going back to the joins, if @Obs matches up to both specified prefname values, we'll see two rows. And that's where the next part comes in:
group by o.obId, o.obName having count(*) = (select count(*) from @prefs where prefname IN ('nodogs', 'waterside') )The use of group by allows for the HAVING clause. That allows for an aggregate function of COUNT(*). We're grouping based on obId and obName (which, since they are tied together, means we're basically grouping on obID). Going back to the above WHERE clause with the joins, if obId matches all prefnames, we should see two rows. So if COUNT(*) = 2, we've got two rows. Rahul hardcoded the 2, Kenneth used a subquery that takes the count of prefname vales (which returns 2). Therefore, only obIDs with 2 rows are displayed. Of course, as you have more or less options, you'll have to modify the WHERE clause and the HAVING clause accordingly.
K. Brian Kelley
@kbriankelley -
Sorry,
srama's solution does not function well when there are more then two parameters
EG: when using ('nodogs', 'waterside', 'nokids')
Kennets solution functions well. The differencence is the comparison (not using count = 2)
count(*) = (select count(*) from @prefs where prefname IN ('nodogs', 'waterside', nokids) )
Still looking for an easier solution, but I don't know if there is any. And for a way to get all as result when the parameter looks like '' (empty string)
-
Rahul's solution works if you change the hard number. For instance, if you have 3 options, change the count to = 3. I'll do some looking around and testing to see if there is an easier solution, but I think Kenneth's is probably the simplest.
K. Brian Kelley
@kbriankelley -
Kenneth, I saw your explanation, and now I understand why srama's solution fits only the "two params sql".
Thank you, your explanation is very clear
-
Brian,
Thank you too.
Your explanation made it even more clear to me.
I miss an INALL function in SQL server (or the Sql standard). But with your help (and others on sqlservercentral) I always find a second best solution
-
Create and use a wanted table e.g.
DECLARE @Wanted TABLE(PrefName VARCHAR(10))
INSERT INTO @Wanted VALUES ('nodogs')
INSERT INTO @Wanted VALUES ('waterside')
SELECT o.obId, o.obName
FROM @Obs o
CROSS JOIN @Wanted w
INNER JOIN @Prefs p ON p.PrefName = w.PrefName
LEFT OUTER JOIN @ObPrefs op ON op.ObID = o.ObID AND op.PrefID = p.PrefID
GROUP BY o.obId, o.obName
HAVING COUNT(*) = SUM(SIGN(ISNULL(op.PrefID,0)))
Far away is close at hand in the images of elsewhere.
Anon. -
David,
That's a nice new idea. I'll try this approach. Thanks
But what does that SUM SIGN ISNULL do? I don't think I understand the effect
-
But what does that SUM SIGN ISNULL do? Because the query uses LEFT OUTER JOIN for @ObPrefs, if there is no match then the columns of @ObPrefs will be null
So, ISNULL(op.PrefID,0) will return either the value if PrefID or zero if null (ie no match)
SIGN will return -1,0,+1 depending on whether the value passed to it is negative, zero or positive, therefore
SIGN(-3) will return -1
SIGN(-2) will return -1
SIGN(-1) will return -1
SIGN(0) will return 0
SIGN(1) will return 1
SIGN(2) will return 1
SIGN(3) will return 1
So in the query SIGN(ISNULL(op.PrefID,0)) will return zero for no match and 1 for a match (assuming you have no negative ID's)
The SUM is used to add them and compare this to the COUNT(*) (total of wanted) to see if there are any houses that have all wanted options
Far away is close at hand in the images of elsewhere.
Anon. -
David, thanks.
I just learned a nice new tric.
I can use it in other situations as well, I think.
-
I do think this is the first time I've seen an inner, left and cross join all in the same query.
Testament to the infinite number of ways there is to phrase a SQL query for the same endresult.
/Kenneth
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply