OUTER JOIN

[747576]

Hi there,
Thanks for the last answers. I need some more help, this time with joins. I have 3 tables to join where a case statement is used to change the value in table 1 to RULE value depending on if the value = the value in table 2. But I need to see all the values in table 1 irrespective if the values are the same or not. I am using left joins but only get nulls for values not satisfying the rules.
Thanks
***********************************************************
TABLE1
ID1, NAME, VALUE
----------------
1 bob 3
2 bob 0.1

TABLE2
NAME, VALUE, RULE
-----------------
bob 0.1 1


TABLE3
NAME, UNITS, EL
----------------
bob cm A

***********************************************************

SELECT T1.ID1
, T3.EL + '_' + T3.UNITS AS NAME
, T3.EL
, T3.UNITS
, CASE WHEN T1.NAME = T2.NAME AND T1.VALUE = T2.VALUE THEN cast(T2.RULE AS float) ELSE T1.VALUE END AS VALUE
FROM TABLE T1 LEFT JOIN
TABLE2 T2 ON T1.NAME = T2.NAME AND T1.VALUE = T2.VALUE
LEFT JOIN TABLE3 T3 ON T2.NAME = T3.NAME

 

[r937]

i'm going to guess that the values aren't equal because FLOAT is an approximate datatype

in other words, 0.1 might actually be stored as 0.09999997



r937.com | rudy.ca

 

[747576]

Hi,
The rule in table 2 should only be true if the value in TABLE1 is equal to TABLE3 i.e. if the value is equal to 0.01 then replace it with '1' otherwise leave the value in TABLE1 as it is i.e. 3
Thanks

 

[r937]

so why isn't table3 mentioned in the CASE?

r937.com | rudy.ca

 

[747576]

Hi,
The Table 3 is in the query to pivot the UNITS and EL columns as well!

 

[r937]

The rule in table 2 should only be true if the value in TABLE1 is equal to TABLE3"

so why isn't table3 mentioned in the CASE?

r937.com | rudy.ca

 

[747576]

No! The rule in table 2 should only be true if the value in TABLE1 is equal to value in TABLE2 where the NAME is the same

 

[r937]

i am completely lost :-(

anyone else?

r937.com | rudy.ca

 

[747576]

This gives me the values I want, it substitutes the value with the rule where TABLE1 value = TABLE2 value, but now I need to add in the columns from table 3 where the NAME is equal to TABLE3 NAME

SELECT T1.ID1
, T1.NAME
, CASE WHEN T1.NAME = T2.NAME AND T1.VALUE = T2.VALUE THEN cast(T2.RULE AS float) ELSE T1.VALUE END AS VALUE
FROM TABLE1 T1 LEFT JOIN
TABLE2 T2 ON T1.NAME = T2.NAME AND T1.VALUE = T2.VALUE