is it possible to use inner join inside case.

Question

is it possible to use inner join inside case.

jaquino012

SSC-Addicted

Points: 431
More actions
March 2, 2018 at 1:20 am

#329529

Hi expert can anyone point me on the right direction, is it possible to use the joins inside select case.
Example:
select A.field1, (case when B.id=3 then inner join table3 C end ) as 'field2' from table1 A inner join table2 B on A.id=B.id Thanks and best regards.

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

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

jaquino012 - Friday, March 2, 2018 1:20 AM
Hi expert can anyone point me on the right direction, is it possible to use the joins inside select case.
Example:
select A.field1, (case when B.id=3 then inner join table3 C end ) as 'field2' from table1 A inner join table2 B on A.id=B.id Thanks and best regards.

SELECT A.field1,

CASE WHEN B.id = 3 THEN c.[column] ELSE [something] END as [field2]

FROM table1 A

INNER JOIN table2 B

ON A.id = B.id

LEFT JOIN table3 C

ON ...

SELECT A.field1,

x.[field2]

FROM table1 A

INNER JOIN table2 B

ON A.id = B.id

OUTER APPLY (

SELECT c.[column] as [field2]

FROM table3 C

WHERE B.id = 3

AND c.[column] = [column from a or b]

) x

SELECT A.field1,

CASE WHEN B.id = 3 THEN (SELECT c.[column] FROM table3 C WHERE c.[column] = [column from a or b]) ELSE [something] END as [field2]

FROM table1 A

INNER JOIN table2 B

ON A.id = B.id

SELECT A.field1,

(SELECT c.[column] FROM table3 C WHERE B.id = 3 AND c.[column] = [column from a or b]) as [field2]

FROM table1 A

INNER JOIN table2 B

ON A.id = B.id

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

jaquino012 SSC-Addicted Points: 431 More actions · Answer 2

ChrisM@Work - Friday, March 2, 2018 1:47 AM
jaquino012 - Friday, March 2, 2018 1:20 AM
Hi expert can anyone point me on the right direction, is it possible to use the joins inside select case.
Example:
select A.field1, (case when B.id=3 then inner join table3 C end ) as 'field2' from table1 A inner join table2 B on A.id=B.id Thanks and best regards.
SELECT A.field1,
CASE WHEN B.id = 3 THEN c.[column] ELSE [something] END as [field2]
FROM table1 A
INNER JOIN table2 B
ON A.id = B.id
LEFT JOIN table3 C
ON ...
SELECT A.field1,
x.[field2]
FROM table1 A
INNER JOIN table2 B
ON A.id = B.id
OUTER APPLY (
SELECT c.[column] as [field2]
FROM table3 C
WHERE B.id = 3
AND c.[column] = [column from a or b]
) x
SELECT A.field1,
CASE WHEN B.id = 3 THEN (SELECT c.[column] FROM table3 C WHERE c.[column] = [column from a or b]) ELSE [something] END as [field2]
FROM table1 A
INNER JOIN table2 B
ON A.id = B.id
SELECT A.field1,
(SELECT c.[column] FROM table3 C WHERE B.id = 3 AND c.[column] = [column from a or b]) as [field2]
FROM table1 A
INNER JOIN table2 B
ON A.id = B.id

Hi

jaquino012 SSC-Addicted Points: 431 More actions · Answer 3

jaquino012 - Friday, March 2, 2018 1:56 AM

ChrisM@Work - Friday, March 2, 2018 1:47 AM
jaquino012 - Friday, March 2, 2018 1:20 AM
Hi expert can anyone point me on the right direction, is it possible to use the joins inside select case.
Example:
select A.field1, (case when B.id=3 then inner join table3 C end ) as 'field2' from table1 A inner join table2 B on A.id=B.id Thanks and best regards.
SELECT A.field1,
CASE WHEN B.id = 3 THEN c.[column] ELSE [something] END as [field2]
FROM table1 A
INNER JOIN table2 B
ON A.id = B.id
LEFT JOIN table3 C
ON ...
SELECT A.field1,
x.[field2]
FROM table1 A
INNER JOIN table2 B
ON A.id = B.id
OUTER APPLY (
SELECT c.[column] as [field2]
FROM table3 C
WHERE B.id = 3
AND c.[column] = [column from a or b]
) x
SELECT A.field1,
CASE WHEN B.id = 3 THEN (SELECT c.[column] FROM table3 C WHERE c.[column] = [column from a or b]) ELSE [something] END as [field2]
FROM table1 A
INNER JOIN table2 B
ON A.id = B.id
SELECT A.field1,
(SELECT c.[column] FROM table3 C WHERE B.id = 3 AND c.[column] = [column from a or b]) as [field2]
FROM table1 A
INNER JOIN table2 B
ON A.id = B.id

Hi ChrisM@Works profile" id="ctl00_ctlContentPlaceHolder_ctl00_ctl00_ctlTopic_ctl00_ctlPanelBar_ctlTopicsRepeater_ctl02_hypUsername" class="i-type-bold" href="https://www.sqlservercentral.com/Forums/Users/ChrisMWork" style="text-decoration: none; color: rgb(0, 51, 102); cursor: pointer; font-weight: 600; font-family: Arial, Helvetica, sans-serif; font-size: 12.8px; font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; letter-spacing: normal; orphans: 2; text-align: left; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px;">ChrisM@Work.
Thanks for the quick reply.
it works

Thanks again.