case with join

Question

Post reply

case with join

Charbel-371270

SSCrazy

Points: 2260
More actions
November 28, 2006 at 12:43 am

#171472

hi
i was wondering . if we have 3 tables table1,table2 and table3
i need to join table1 to either table2 or tbale3 depending on a certain condition,can we do it using the case method?
i mean like
select * from table1
if case1 then join table2
else join table3

is there a way we can do it like this?
thanks

If something's hard to do, then it's not worth doing.

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Probably the easiest is to do the if outside of the query

IF (condition)

SELECT <fields> from table1 inner join table2 ON...

ELSE

SELECT <fields> from table1 inner join table3 ON...

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Sergiy SSC Guru Points: 110208 More actions · Answer 2

You better read a little bit about design of relational databases.

_____________
Code for TallyGenerator

Jeff Block Old Hand Points: 334 More actions · Answer 3

if the conditional is within the data of tables 2 and 3, and are mutually exclusive, then a left outer join with the appropriate where clause will do the trick

Charbel-371270 SSCrazy Points: 2260 More actions · Answer 4

no the condition is on table1

on a certain feild of table1

If something's hard to do, then it's not worth doing.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Could you maybe post thr structure of you tables, some sample data and an indication of what you're trying to achieve?

As Sergiy said, conditional joins is not somethign that should be necessary in a properly designed relational database

Thanks

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Bhudev Prakash SSC-Addicted Points: 471 More actions · Answer 6

Hi,

U can give breath to you will. Simply make a Stored Procedure. I'm giving the smaple query as below...

CREATE PROCEDURE mst_spJoinTableConditionally

@Condition NVARCHAR(2000)

AS

DECLARE @Table NVARCHAR(200)

SELECT @Table = CASE @Condition WHEN 'Condition 1' THEN 'TABLE1 A INNER JOIN TABLE2 B ON A.Table1Id = B.Table1Id' WHEN 'Condition 2' THEN 'TABLE1 A LEFT OUTER JOIN TABLE3 B ON A.Table1Id = B.Table1Id' END

EXEC('

SELECT

A.*,B.*

FROM ' + @Table + '

')

GO

Like this U can manuplate tables columns.

Good Luck do Ur Best.

Bhudev.Prakash@hotmail.com

Jules Bonnot SSCertifiable Points: 5488 More actions · Answer 7

why not use a union

so

select * from table1 join table2 on table1.col = yourcondition

union

select * from table1 join table3 on table1.col = yourcondition

www.sql-library.com[/url]

Bhudev Prakash SSC-Addicted Points: 471 More actions · Answer 8

If U'll Learn to play with Variables U'll learn lot more.

DECLARE @Table NVARCHAR(128)

SELECT @Table =

CASE Condition

WHEN 'Condition 1' THEN 'Table1'

WHEN 'Condition 2' THEN 'Table2'

ELSE 'Table3'

END

EXEC('SELECT * FROM ' + @Table + '')

Bhudev Prakash [bhudev.prakash@hotmail.com]

Vladan SSC-Insane Points: 22072 More actions · Answer 9

Yes, you can use variables and dynamic SQL... but in this case I would say "unfortunately you can". If you need to resort to such approach, something is wrong with the database design.

Either the two tables (2 and 3) should in fact be one table with some "Type" column (with values e.g. 2 or 3), or you should have 2 columns in Table1 where you have only 1 now... or maybe the design itself is OK and then the query can be written without "conditional" join, you just don't know how. Hard to tell which one it is unless you post the DDL and explain what the tables and columns mean.

Bhudev Prakash SSC-Addicted Points: 471 More actions · Answer 10

Hi Dear Suppose there is three tables eg.: Table1..2..3

and One has 5 Columns, Two has 6 columns and Three has 9.

And here I supposed to there is some Link column between the tables.

First I'll analysis my requierement how many columns are there to display as output.

And If condition true then from which table which columns has to be pick.

TABLE1 TABLE2 TABLE3

--> t1Col1 --> t2Col1 --> t3Col1

--> ...... --> ...... --> ......

--> t1Col5 --> t2Col6 --> t3Col9

Suppose I've have to fetch max number of colum 5+6+9 = 20 with in all conditions.

CREATE PROCEDURE mst_spJoinTableConditionally

@Condition NVARCHAR(2000),

@BusinessLogic NVARCHAR(20) -- 20 times 1s or 0s ---> 1 = Enable 0 = Disable

AS

DECLARE

@Table NVARCHAR(200),

@Col1 NVARCHAR(128),

@Col2 NVARCHAR(128),

..................,

@Col20 NVARCHAR(128)

SET @Col1 = 'A.t1COL1,'

SET @Col2 = 'A.t1COL2,'

SET @Col3 = 'A.t1COL3,'

SET @Col4 = 'A.t1COL4,'

SET @Col5 = 'A.t1COL5,'

SET @Col6 = 'B.t2COL1,'

SET @Col7 = 'B.t2COL2,'

SET @Col8 = 'B.t2COL3,'

SET @Col9 = 'B.t2COL4,'

SET @Col10 = 'B.t2COL5,'

SET @Col11 = 'B.t2COL6,'

SET @Col12 = 'C.t3COL1,'

SET @Col13 = 'C.t3COL2,'

SET @Col14 = 'C.t3COL3,'

SET @Col15 = 'C.t3COL4,'

SET @Col16 = 'C.t3COL5,'

SET @Col17 = 'C.t3COL6,'

SET @Col18 = 'C.t3COL7,'

SET @Col19 = 'C.t3COL8,'

SET @Col20 = 'C.t3COL9,'

SET @Col20 = 'C.t3COL10'

---> U can manage it conditionally as well through business logic made by devloper.

IF LEFT(@BusinessLogic,1) = '0' SET @Col1 = 'NULL,'

IF RIGHT(1,LEFT(@BusinessLogic,2)) = '0' SET @Col2 = 'NULL,'

.......................................................

IF RIGHT(@BusinessLogic,1) = '0' SET @Col20 = 'NULL'

SELECT @Table =

CASE @Condition

WHEN 'Condition 1'

THEN 'TABLE1 A INNER JOIN TABLE2 B ON A.Table1Id = B.Table1Id'

WHEN 'Condition 2'

THEN 'TABLE1 A LEFT OUTER JOIN TABLE3 C ON A.Table1Id = C.Table1Id'

END

EXEC('

SELECT

Col1 = ' + @Col1 + '

Col2 = ' + @Col2 + '

Col3 = ' + @Col3 + '

Col4 = ' + @Col4 + '

Col5 = ' + @Col5 + '

Col6 = ' + @Col6 + '

Col7 = ' + @Col7 + '

Col8 = ' + @Col8 + '

Col9 = ' + @Col9 + '

Col10 = ' + @Col10 + '

Col11 = ' + @Col11 + '

Col12 = ' + @Col12 + '

Col13 = ' + @Col13 + '

Col14 = ' + @Col14 + '

Col15 = ' + @Col15 + '

Col16 = ' + @Col16 + '

Col17 = ' + @Col17 + '

Col18 = ' + @Col18 + '

Col19 = ' + @Col19 + '

Col20 = ' + @Col20 + '

FROM ' + @Table + '

')

It is only a concept - Depands upon your logic/requirement U may devlop.