Jacob Alley Jacob Alley - 2 months ago 7
SQL Question

Display data based on data in a different table, using a case statement

I have multiple tables, one has the columns ID/Member/Type/UseDefault.
The other table has columns with descriptive information about a person, based on the ID.

Type will mostly be defualt. In which case, the information that corresponds with ID is perfectly fine. If UseDefault is false (which will only happen when type IS NOT defualt AND they do not want to use the default value), then the supplied ID is also perfectly suitable. However, if type is not default, but use default is entered as Yes, i need to display the information that is associated with the same member's default ID.

For example:
Example

ID Member Type UseDefault

1 joe default Y
2 joe additional Y
3 joe third N


As you can see...for row 1, use Default is Yes, but the type is already default.

In row 3, use default is No, so you continue with ID 3.

However in row 2. Use default is yes, and the type is not default, so we need to find the row that is Joe's default type. Here is the other table, and the expected output from my query.

Info

ID Car Color Food

1 Benz Red Pizza
2 Ferrari Green Cheese
3 Minivan Orange Hamburger

expected output:

Output

ID Car Color Food

1 Benz Red Pizza
1 Benz Red Pizza
3 Minivan Orange Hamburger


basically i have

declare
id varchar2(2000)

Select I.Car, I.Color, I.FavoriteFood
from Info I
full outer join Example e on e.id = i.id
where case id:= when (e.useDefault = 'N' AND e.Type <> 'Default') then (select id from example where e.name = name and e.type = default) else e.type end
and i.id = @id;

Answer
with
     inputs ( id, member, type, usedefault ) as (
       select 1, 'joe', 'default'   , 'Y' from dual union all
       select 2, 'joe', 'additional', 'Y' from dual union all
       select 3, 'joe', 'third'     , 'N' from dual
     ),
     info ( id, car, color, food ) as (
       select 1, 'Benz'   , 'Red'   , 'Pizza'     from dual union all
       select 2, 'Ferrari', 'Green' , 'Cheese'    from dual union all
       select 3, 'Minivan', 'Orange', 'Hamburger' from dual
     ),
     defaults ( id, member ) as (
       select id, member
       from   inputs
       where  type = 'default'
     ),
     prep ( id_to_use ) as (
       select case when i.usedefault = 'N' then i.id
                   else d.id end   as id_to_use
       from   inputs i inner join defaults d on i.member = d.member
     )
select p.id_to_use as id, i.car, i.color, i.food
from   prep p left outer join info i on p.id_to_use = i.id
;