SubQuery returns more than 1 value

  • Hi,

    I'm looking for a little help with this query. I'm trying to write a query that returns true if a visit exists in a Fact table and not in an error table and has a message NOT LIKE 'w%' otherwise it returns false

    I have this query which returns all the visits that would be false visits but I need to output to be false not the visit number.

    SELECT E.VisitID

    FROM Fact F LEFT OUTER JOIN Error E

    ON F.VisitID = E.VisitID

    WHERE E.VisitID IS NOT NULL AND E.Message NOT LIKE 'w%'

    I was thinking I could do a CASE Like this

    SELECT CASE

    WHEN F.VisitID = (SELECT F.VisitID

    FROM Fact F LEFT OUTER JOIN Error E

    ON F.VisitID = E.VisitID

    WHERE E.VisitID IS NOT NULL AND EEPE.MessageNumber NOT LIKE 'w%')THEN 'False'

    ELSE 'True'

    END

    FROM Fact F JOIN Error E

    ON F.VisitID = E.VisitID

    But that give me the problem of SubQuery returns more than 1 value.

    Can anyone please point me a better direction or give me some help with my query?

    Thanks!

  • You could use and EXISTS in you case statement rather than an =

    Not sure if I've got you logic right, but something like the following

    -- Some Test Data

    ;with Fact as (

    SELECT *

    FROM (VALUES (1), (2), (3), (4), (5)) AS Fact(VisitID)

    )

    , Error as (

    SELECT *

    FROM (VALUES (1,'a'), (3,'w also has another'), (3,'a'), (4, 'w just this one'), (5,'a')) AS Error(VisitID, [Message])

    )

    -- The Query

    SELECT f.VisitID, CASE

    WHEN EXISTS(SELECT 1 FROM Error e WHERE e.visitID = f.visitID and [Message] not like 'w%') THEN 'false'

    ELSE 'true'

    END

    FROM Fact f

  • Thanks That fixed it

  • No Problem

  • good one micky....

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply