Either/ Or Query

  • Hi, I have a table containing some columns and some simple data. I want to select from the table by specifying the JobDomain and if there is a TestType  = 'B', then just return that record. If there isn't a TestType = 'B', the return all records for that JobDomain

    So, if JobDomain = 1, I just want to return the row where JobDomain = 1, TestType = 'B' and JobIndex = 126

    If the JobDomain = 2, I want to return the 4 rows where JobDomain = 2, testType = 'A' and JobIndex, 123, 124, 125, 127

    How do I do this?

    CREATE TABLE TestTable(JobDomain int, TestType varchar(5), JobIndex int)

    INSERT INTO TestTable(JobDomain, TestType, JobIndex) VALUES (1, 'A', 123)

    INSERT INTO TestTable(JobDomain, TestType, JobIndex) VALUES (1, 'A', 124)

    INSERT INTO TestTable(JobDomain, TestType, JobIndex) VALUES (1, 'A', 125)

    INSERT INTO TestTable(JobDomain, TestType, JobIndex) VALUES (1, 'B', 126)

    INSERT INTO TestTable(JobDomain, TestType, JobIndex) VALUES (1, 'A', 127)

    INSERT INTO TestTable(JobDomain, TestType, JobIndex) VALUES (2, 'A', 123)

    INSERT INTO TestTable(JobDomain, TestType, JobIndex) VALUES (2, 'A', 124)

    INSERT INTO TestTable(JobDomain, TestType, JobIndex) VALUES (2, 'A', 125)

    INSERT INTO TestTable(JobDomain, TestType, JobIndex) VALUES (2, 'A', 127)

  • Here's a quick way:

    DECLARE @JobDomain int
    SET @JobDomain = 1

    ;WITH cte_Type_B AS (
    SELECT *
    FROM dbo.TestTable
    WHERE JobDomain = @JobDomain AND TestType = 'B'
    ),
    cte_Type_Other AS (
    SELECT *
    FROM dbo.TestTable
    WHERE JobDomain = @JobDomain AND NOT EXISTS(SELECT 1 FROM cte_Type_B)
    )
    SELECT *
    FROM cte_Type_B
    UNION ALL
    SELECT *
    FROM cte_Type_Other

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who give you the shirt off *someone else's* back.

  • Scratched the answer that was here... I didn't read the post correctly.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Dear Lord... I'm a production DBA. Please grant me patience because, if you grant me strength, I'm gonna need bail money to go with it.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Or slightly more concisely

    DECLARE @JobDomain int
    SET @JobDomain = 1

    SELECT *
    FROM dbo.TestTable
    WHERE JobDomain = @JobDomain
    AND (TestType = 'B'
    OR NOT EXISTS(SELECT *
    FROM dbo.TestTable
    WHERE JobDomain = @JobDomain
    AND TestType = 'B'))
  • Guys... the OP doesn't know which domains contain a "B" ahead of time.  He was just explaining the desired results for the test data he provided.  Or, at least, that's how I read it.

    See Jonathan's post below.  I believe I'm going to go to be early tonight {headdesk}.

     

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Dear Lord... I'm a production DBA. Please grant me patience because, if you grant me strength, I'm gonna need bail money to go with it.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden wrote:

    Guys... the OP doesn't know which domains contain a "B" ahead of time.  He was just explaining the desired results for the test data he provided.  Or, at least, that's how I read it. 

    dec_obrien wrote:

    I want to select from the table by specifying the JobDomain and if there is a TestType  = 'B', then just return that record. If there isn't a TestType = 'B', the return all records for that JobDomain

  • Heh... I've been having issues with caffeine absorption lately. 😀

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Dear Lord... I'm a production DBA. Please grant me patience because, if you grant me strength, I'm gonna need bail money to go with it.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Hi,

    Thanks for the replies. Both approaches work (Scott's & Jonathan's). I've never used EXISTS before so must have a play with it. I also didn't know you could use a CTE like in Scott's answer as in define 2 in the same query.

    As they say in my part of the world: every day's a school day!!

  • Jeff Moden wrote:

    Heh... I've been having issues with caffeine absorption lately. 😀

     

    Too much ? Or not enough ?

  • homebrew01 wrote:

    Jeff Moden wrote:

    Heh... I've been having issues with caffeine absorption lately. 😀

    Too much ? Or not enough ?

     

    Both, actually.  Too much in the morning and too little in afternoon/evening, and night.  I'm considering just hooking up an IV. 😀

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Dear Lord... I'm a production DBA. Please grant me patience because, if you grant me strength, I'm gonna need bail money to go with it.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 10 posts - 1 through 9 (of 9 total)

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