CODEWITHSUNDEEP
sqljoinleft-joininformix
Micheal
Micheal

Reputation: 2322

Informix multiple joins

I need to modify my query to add other join. But the new join probably needs to be a left join. I am unsure how to go about this. I am using Informix:

     set    query   "SELECT DISTINCT x.xfertype,x.app_type,x.service_type,x.lang,x.area_type,x.area_value,x.module,x.field1,x.field2,x.disabled,a.frames,a.allocs,a.term_id,t.term,c.center_id,c.center_name,a.message_id,x.field3,x.apn_type,x.global, a.icm, s.group_name "
    append  query   " FROM test_xfertypes AS x, test_allocation AS a, test_terms AS t, test_callcenter AS c"
    append  query   " AND a.xfertype = x.xfertype "
    append  query   " AND a.term_id = t.term_id "
    append  query   " AND t.center_id = c.center_id ";

test_xfertypes AS x contains area_value (int)

I want to left join the above table with another new table test_routing_groups AS s.

I want to left join such that it returns s.group_name WHERE x.area_value IN (s.area_id); if the group_name exists return the group_name otherwise return null.

Upvotes: 3

Views: 5332

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

You need to use the standard join syntax for this. Your query should look like:

SELECT DISTINCT x.xfertype, x.app_type, x.service_type, x.lang,x.area_type,
       x.area_value, x.module, x.field1, x.field2, x.disabled,
       a.frames, a.allocs, a.term_id,
       t.term,c.center_id, c.center_name,a.message_id, x.field3, x.apn_type,x.global,
       a.icm, s.group_name
FROM test_xfertypes x join
     test_allocation a
     on a.xfertype = x.xfertype join
     test_terms t
     on a.term_id = t.term_id join
     test_callcenter c
     on t.center_id = c.center_id

You can left join another table just by adding:

left outer join test_routing_groups s
on x.area_value IN (s.area_id)

You can also use an "x.area_value = s.area_id" rather than the "in" clause.

Upvotes: 3

Related Questions