November 28, 2006 at 12:43 am
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
November 28, 2006 at 1:13 am
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
November 28, 2006 at 1:49 am
You better read a little bit about design of relational databases.
_____________
Code for TallyGenerator
November 28, 2006 at 10:48 pm
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
November 29, 2006 at 3:16 am
November 29, 2006 at 3:48 am
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
November 29, 2006 at 5:50 am
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.
November 29, 2006 at 7:26 am
November 30, 2006 at 2:09 am
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]
November 30, 2006 at 5:57 am
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.
November 30, 2006 at 6:50 am
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.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply