CASE JOIN ??? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

CASE JOIN ???

Author

Topic

wbb1975
Starting Member

23 Posts

Posted - 2002-09-09 : 07:35:07

Hi,

I have a table with one field where there is a special type to clarify the entries in the table, depending on the value of this field I'd like to create joins to 1:1 tables; I need to use a stored procedure ...

TABLE1
*******
TableOneKey
Type

TABLE2 (1:1 to TABLE1)
********
TableTwoKey
TableOneForeignKey
Type

TABLE3
*********
TableThreeKey
TableOneForeignKey
Type

I hope it is clear what I mean, so thanks for your suggstions and finding the easiest way ... ;-)

Greetings
Stefan

nr
SQLTeam MVY

12543 Posts

Posted - 2002-09-09 : 07:53:32

something like

select TableTwoKey ,
TableOneForeignKey ,
Type
from TABLE1 join TABLE2 on TableOneKey = TableOneForeignKey
and TABLE1.Type = ...
union
select TableThreeKey ,
TableOneForeignKey ,
Type
from TABLE1 join TABLE2 on TableOneKey = TableOneForeignKey
and TABLE1.Type = ...

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

wbb1975
Starting Member

23 Posts

Posted - 2002-09-09 : 09:10:01

Thanks, would be a nice solution, but unfortunately the linked tables do have different columns-structures .... so I cannot use UNION ...

Greetings

Stefan

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2002-09-09 : 09:24:59

Have you considered a correlated subquery?

wbb1975
Starting Member

23 Posts

Posted - 2002-09-09 : 11:39:54

thanks, but no I do need another solution, what I want is the following (somehow similar like the code below although certainly the code below does not work)

SELECT *, t1.QuestionType FROM TABLE1 t1
INNER JOIN
!!!
CASE t1.QuestionType
WHEN "Case 1" ... *do inner join to table 2*
WHEN "Case 2" ... *do inner join to table 3*
...
!!!

Thanks & Greetings

Stefan

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-09-09 : 12:12:07

How about:

SELECT DISTINCT T1.Type,
COALESCE(T2.col, T3.col) AS col
FROM Table1 T1
LEFT JOIN Table2 T2 ON T1.TableOneKey=T2.TableOneForeignKey AND T1.Type=T2.Type
LEFT JOIN Table3 T3 ON T1.TableOneKey=T3.TableOneForeignKey AND T1.Type=T3.Type

This assumes that the Type column is part of the join criteria based on the structure you provided...if that's not your actual table structure you must post them in order for us to help. You should also post some sample data for each table and the desired output.

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-09-09 : 12:23:10

Or maybe this:


SELECT *, T1.Type
FROM Table2 T2
FULL OUTER JOIN Table3 T3 ON 1 = 0
INNER JOIN Table1 T1 ON T1.TableOneKey =
    COALESCE(T2.TableOneForeignKey, T3.TableOneForeignKey)
  AND T1.Type = COALESCE(T2.Type, T3.Type)

wbb1975
Starting Member

23 Posts

Posted - 2002-09-09 : 12:26:04

OK, thanks guys for your help, but unfortunately this is not what I meant

OK here's again (a little bit more detailed) the table structure:

TABLE 1:
*********************
Table1ID (Primary Key), int
Field1
Field2
Field3
ItemType varchar(20)

TABLE 2:
*********************
Table2ID (Primary Key), int
Table1_ForeignKey, int (links 1:1 to Table1ID of Table 1)
Field20
Field21
BitField2
BitField3

TABLE 3: (the same structure as table 2 but with different fields)
*********************
Table2ID (Primary Key), int
Table1_ForeignKey, int (links 1:1 to Table1ID of Table 1)
Field77
Field88
and so on (I do have many of these tables ...)

THANK YOU ALL GUYS FOR YOUR HELP

Greetings

STefan

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2002-09-09 : 13:13:59

Have you considered Dynamic SQL?

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-09-09 : 14:12:41

Obviously, you've confused your audience.

You need to: