Help with simple SQL query

[Dudek]

Hi there..


Was wondering whether anyone can help on a simple (i hope) SQL problem here

i have 4 tables,

tablea | tableb | tablec
idx1 value | idx2 idx1 | idx3 idx2
1 ab | 22 1 | 444 22
2 bc | 33 2 | 555 33
3 de | 44 3 | 666 22
4 fg | |

tabled
key name
122444 alex
233555 smith
122666 alan
344 max
4 tom

Note: idx does not mean index.. its just a column name

Table B is refernced by table A by idx1
Table C is reference by table B by idx2

what i need is to select/create a view that will give an output of:

idx1 idx2 idx3 name
1 22 444 alex
2 33 555 smith
1 22 666 alan
3 44 null max
4 null null tom

as it can be seen, the key of table d to get the name is the concatenation
of a string of referenced/related idx1, idx2 and idx3

Anybody can help me here?
Thanks

JD

 

[dbmsguy]

select a.idx1, b.idx2, c.idx3, d.name

where a.idx1=b.idx1 and
b.idx2=c.idx2 and
rtrim(convert(char(4),a.idx1))+rtrim(convert(char(4),b.idx2))+rtrim(convert(char(4),c.idx3)) = rtrim(convert(char(4),d.key))


something like the above should work. you are just converting to char and then doing the test. I didn't actually try it, but it should work. you need the rtim to get rid of trailing spaces. i also asume the keys are int. Paul

http://www.dbmsguy.com