Select , populate from diff tables based on condition

shashi_s · #1 (**permalink**) 04-10-07, 07:07

I have 3 tables....
table 1: jobs
columns: job_id, customer_id, site_Id;
table 2: customers
columns: customer_id, address1, state, postcode
table3: addresses
columns: site_id, address1, state, postcode

My select statement should return job_id, address1, state, postcode
from jobs as j, customers as c, addresses as a.

If j.site_id > 1
then a.address1, a.state, a.postcode should be filled
else c.address1, c.state, c.postcode should be filled

any solution on how to do it?

RedNeckGeek · #2 (**permalink**) 04-10-07, 07:19

Does the platform you are using support the CASE statement?

shashi_s · #3 (**permalink**) 04-10-07, 07:24

yes it does....

gvee · #4 (**permalink**) 04-10-07, 07:30

CASE statements, as RedNeckGeek suggested, are SQL's version of an If statement.
The syntax is generally

Code:

CASE WHEN <criteria> THEN <output when true> ELSE <output when false> END

r937 · #5 (**permalink**) 04-10-07, 07:41

CASE will not do the job here because it is a question of joining to one table or the other, and in order to use CASE as suggested you'd have to join to them both

there's a different approach:

Code:

select a.address1, a.state, a.postcode 
  from jobs
inner
  join addresses as a
    on a.site_id = jobs.site_id
 where jobs.site_id > 1
union all
select c.address1, c.state, c.postcode 
  from jobs
inner
  join customers as c
    on c.customer_id = jobs.customer_id
 where jobs.site_id <= 1