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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
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) A socialist is someone who will give you the shirt off *someone else's* back.
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