Subquery in WHERE clause not working

  • 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

  • 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)

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • 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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • 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