Querying tables based on other column values

Question

Is there a way to query different databases based on the value of a column in the query?

Say for example you have the following columns:

• id
• part_id
• attr_id
• attr_value_ext
• attr_value_int

You then run a query and if the attr_id is '1' is returns the attr_value_int column but if attr_id is greater than '1' it joins data from another table based on the attr_value_ext.

Answer 1

hard to give an exact answer based on that description...

you should be able to do that with a UNION

select stuff from one table where attr_id = 1
UNION
select stuff from another table where attr_id > 1

Answer 2

Nothing in the SQL spec, but there are often DB specific functions that will do this for you. For example, decode on oracle would work for you.

Answer 3

something like this?

select case when a.id = 1 then a.attr_value_int
            when a.id > 1 then b.some_other_col
            else null end as whatever
from first_table a
     left outer join other_table b
     on ( a.attr_val_ext = b.id )
/

Answer 4

You could use a conditional in the on clause, like:

select    case when attr_id = 1 then attr_value_int
               when attr_id = 2 then t1.value_int
               when attr_id = 3 then t2.value_int
          end
from      YourTable yt
left join Table1 t1 
on        t1.attr_id = 2 and yt.part_id = t1.part_id
left join Table2 t2 
on        t1.attr_id = 3 and yt.part_id = t2.part_id

This will work best if the number of tables is relatively small, and known in advance. Otherwise you'll have to resort to dynamic SQL, or building a follow up query from the client.

Answer 5

SELECT ID,PART_ID,ATTR_ID,CASE
  WHEN attr_id =1 THEN attr_value_int  
  WHEN attr_id >1 THEN <SELECT QUERY>
  END <My Column>
from TABLE;

Maybe this will work