Jacob Alley Jacob Alley - 10 months ago 53
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:

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.


ID Car Color Food

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

expected output:


ID Car Color Food

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

basically i have

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 Source
     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