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.
| Author |
Topic |
|
inrsence
Starting Member
48 Posts |
 Posted - 2001-07-20 : 17:07:51
|
| Hi,I'm just thinking about some thigns I need to design into a new db and was curious about doing a "condiitonal join". I think this might be accomplished with a CASE statement.Essentialy the situation might be where I have:A--------------A_ID int (PK)Type bitA_Values--------------ValID int (PK)A_ID int (FK)Val varchar(100)B--------------B_ID int (PK)A_ID int (FK)B_Nums--------------B_ID int (PK)BNum intThe goal here is to be able to use a value from A_Vals when the Type=0 (string) and a value from B_Nums when Type=1 (numeric).I was thinking I could do this like this.. but this is a first stab and this is more academic than practical:SELECT B_ID, CASE Type WHEN 0 THEN Val ELSE BNum END AS ValFROM ((B INNER JOIN A on B.A_ID = A.A_ID) LEFT JOIN B_Nums bn ON B.B_ID = bn.B_ID) LEFT JOIN A_Values av ON A.A_ID = av_A_IDThe next question, and perhaps the harder one, is how do I query out in the other direction..Referencing B_Nums: SELECT B_ID FROM B WHERE Val = 1234as opposed to referencing A_Values: SELECT B_ID FROM B WHERE Val = 'yellow'My question here is, I guess, how to query and know which table to check based on the type of value that A_ID represents (either 0 or 1).I'm trying to figure out the best way to arrange the data and right now I'm fried on this issue.TIA for any input.greg |
|
|
|
|
|
Conditional Joins?