September 5, 2007 at 12:36 pm
Hello,
i have 2 tables (V_CONFIGURATIONITEM and V_VALUEMAPPING) and i want to do several joins to have the info in 1 table:
I'm thinking that i see it to difficult and that evrything can be made more easely and more efficient....
V_CONFIGURATIONITEM (TABLE)
OBJECTID (KEY) CCICODE1 CCICODE2 CCICODE3 CCICODE4
1 (12) (13) (14) (15)
... 2 (21) (1) (2) (45)
---------------------------
V_VALUEMAPPING (TABLE)
OBJECTID (KEY) INTERNALVALUE EXTERNAL_VALUE
1 (12) (H.P.)
2 (13) (IBM)
3 (14) (ALTIRIS)
4 (15) (BMC)
...
select * from v_configurationitem
inner join v_valuemapping v1 on v_valuemapping.INTERNALVALUE = v_configurationitem.ccicode1
inner join v_valuemapping v2 on v_valuemapping.INTERNALVALUE = v_configurationitem.ccicode2
inner join v_valuemapping v3 on v_valuemapping.INTERNALVALUE = v_configurationitem.ccicode3
inner join v_valuemapping v4 on v_valuemapping.INTERNALVALUE = v_configurationitem.ccicode4
---------------------------------------------------------------------------------------
Meaning to consult the following values later:
select v1.EXTERNAL_VALUE
select v2.EXTERNAL_VALUE
select v3.EXTERNAL_VALUE
select v4.EXTERNAL_VALUE
Thx already for your help
Greetz.
Steve
September 6, 2007 at 1:15 am
Well, not sure, but maybe you want to do something like this?
select cfg.*, v1.EXTERNAL_VALUE, v2.EXTERNAL_VALUE, v3.EXTERNAL_VALUE, v4.EXTERNAL_VALUE
from v_configurationitem cfg
inner join v_valuemapping v1 on v1.INTERNALVALUE = cfg.ccicode1
inner join v_valuemapping v2 on v2.INTERNALVALUE = cfg.ccicode2
inner join v_valuemapping v3 on v3.INTERNALVALUE = cfg.ccicode3
inner join v_valuemapping v4 on v4.INTERNALVALUE = cfg.ccicode4
You need to use alias for the table that is joined several times, right when you define the join - v1.INTERNALVALUE, not v_valuemapping.INTERNALVALUE.
Sorry for the * in select, it shouldn't be there, but you didn't supply any info about columns you want to display from the configuration table.
PS: I didn't comment on database design, just wrote how to do a select from existing structure. Placing 4 columns into a table, all pointed to the same reference table, is not the best solution and forces you to join the same table 4 times. But it is quite probable that you have to live with this structure and can't influence it...
September 6, 2007 at 2:19 pm
Hello Vladan,
thx very much for your answer
it helped me a lot already
there is only one question i am having,
i did this with a left join because i was interested in the v_configurationitem table
select cfg.*, v1.EXTERNAL_VALUE, v2.EXTERNAL_VALUE, v3.EXTERNAL_VALUE, v4.EXTERNAL_VALUE
from v_configurationitem cfg
left join v_valuemapping v1 on v1.INTERNALVALUE = cfg.ccicode1
left join v_valuemapping v2 on v2.INTERNALVALUE = cfg.ccicode2
left join v_valuemapping v3 on v3.INTERNALVALUE = cfg.ccicode3
left join v_valuemapping v4 on v4.INTERNALVALUE = cfg.ccicode4
but now he gives me to many results (too many records), isn't there a way to do a group by but do i can keep the info of the 4 fields in one record??
Thx
Steve
September 7, 2007 at 12:16 am
I am not sure if I am understanding your required result set correctly but
You could do something like
select cfg.*,
Value1 = Min(
Case v1.INTERNALVALUE
When cciCode1 Then v1.EXTERNAL_VALUE
Else null
End
)
, etc...
from v_configurationitem cfg
left join v_valuemapping v1
on v1.INTERNALVALUE in( cfg.ccicode1, cfg.ccicode2, cfg.ccicode3, cfg.ccicode4 )
You will have to check on performance.....
September 7, 2007 at 1:42 am
I think that in fact LEFT JOIN is more appropriate here, because otherwise only those rows of condiguration table that have valid entry in all 4 CCI columns will be displayed.
Using LEFT JOIN in your situation shouldn't produce duplicates for which you would need to group by - at least I think so, but I know very little about the data in your tables. Simply ALL rows from the cofiguration table are displayed - by what column would you like to group by, and why?
Maybe you could post some sample data, and required result?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply