Either/ Or Query

Question

Either/ Or Query

dec_obrien

Default port

Points: 1486
More actions
December 7, 2022 at 8:30 pm

#4123081

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)

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

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

ScottPletcher SSC Guru Points: 101103 More actions · Answer 1

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) "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".

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 2

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.

Change is inevitable... Change for the better is not.

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

Jonathan AC Roberts SSCoach Points: 18356 More actions · Answer 3

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

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 4

~~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.

Change is inevitable... Change for the better is not.

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

Jonathan AC Roberts SSCoach Points: 18356 More actions · Answer 5

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

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 6

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.

Change is inevitable... Change for the better is not.

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

dec_obrien Default port Points: 1486 More actions · Answer 7

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!!

homebrew01 SSC Guru Points: 55548 More actions · Answer 8

Jeff Moden wrote:

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

Too much ? Or not enough ?

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 9

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.

Change is inevitable... Change for the better is not.

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