Hi everyone: This is in SQL 2016. The following query is returning no results:
SELECT
*
FROM
Properties
WHERE
Type = 'RH'
AND Publish = 1
AND NotForSale = 0
AND LotStatus = 'Spec'
AND PlanID IN
(SELECT
REPLACE(CONCAT('''(', PlanNumber, ',', RelatedPlanNumbers, ')'''), ',', ''',''')
FROM
Plan
WHERE
ItemID = 6130)
When I run the subquery in the WHERE clause by itself, it returns the results I was expecting: 'S75000000','S74000000'
When I substitute the results of the subquery for the subquery (AND PlanID IN ('S75000000','S74000000') and run the entire query, I get the results I expect (7 rows).
I don't understand why the subquery works on its own but does not work when as part of the WHERE clause. What am I missing?
Thanks!
Amy
August 13, 2020 at 9:05 pm
You don't need to include the brackets or commas in the subquery – you only need it to return the values you're comparing against.
Select ...
Where x in (select x from y)
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
August 13, 2020 at 9:27 pm
It looks like you have 2 columns in each row from the Plan table that you want to check against. Or maybe it is just one row in the Plan table (ItemID = 6130) - but 2 columns.
Either way - you cannot use a sub-query in an IN statement to check against multiple columns. The query must return a single column with as many rows as needed - but still only a single column.
The first option would be to use UNION ALL and query for the PlanNumber and RelatedPlanNumbers:
Select *
From Properties p
Where Type = 'RH'
And Publish = 1
And NotForSale = 0
And LotStatus = 'Spec'
And PlanID In ( Select pl.PlanNumber
From plan pl
Where ItemID = 6130
Union All
Select pl.RelatedPlanNumbers
From plan pl
Where ItemID = 6130);
If the PlanNumber and the RelatedPlanNumbers are the same - this will still work because your PlanID will be in the set.
The better option is to switch to a correlated sub-query using EXISTS:
Select *
From Properties p
Where Type = 'RH'
And Publish = 1
And NotForSale = 0
And LotStatus = 'Spec'
And Exists (Select *
From plan pl
Where ItemID = 6130
And (
pl.PlanNumber = p.PlanID
Or pl.RelatedPlanNumbers = p.PlanID
));
Using EXISTS will probably perform better as well - since it will stop checking as soon as it finds a match. It also is much easier to manage and maintain since you don't have the UNION ALL.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
You'll need to split the list of RelatedPlanNumbers, but that's easy enough:
AND PlanID IN
(SELECT
ca1.PlanNumber
FROM
[Plan]
CROSS APPLY (
SELECT PlanNumber
UNION
SELECT Item
FROM dbo.DelimitedSplit8K(RelatedPlanNumbers, ',') ds
) AS ca1
WHERE
ItemID = 6130
)
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
August 14, 2020 at 1:53 pm
Thanks Scott! Your response was exactly what I needed. Perfect!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply