Only output specific Values using SQL-CodePudding

I am trying to work out how the SQL if I want to bring back ALL "Names" from table1 and only list the output in Column 2 to contain the value if it is "Apple". If its not "Apple" or is "Null" then column 2 should also be "Null"

Table 1

ID(pk)	Name
24	Boris
25	Dominic
26	Rishi
27	Elizabeth
28	Ben

Table 2

ID(fk)	Description
27	Apple
27	Orange
27	Pear
26	Apple
26	Pear
25	Pear
24	Orange

Required Output

Name	Description
Boris
Dominic
Rishi	Apple
Elizabeth	Apple
Sajid

CodePudding user response：

You can solve it in a variety of ways, one example:

SELECT t1.name
     , MAX(CASE t2.descripton WHEN 'Apple' THEN t2.descripton END)
FROM t1
JOIN t2
    USING(id)
GROUP BY t1.name;

The CASE expression will map all but Apple to null. MAX will then reduce the set to either Apple or null for each name.

CodePudding user response：

I think below query will work for you:

Select Name, CASE WHEN ID IN (SELECT DISTINCT ID FROM table2 WHERE LOWER(description) = 'apple') THEN 'Apple' ELSE Null END as Description FROM table1;

CodePudding user response：

If there are no duplicates in the second table (in a comment below the question you confirmed that this is the case), this is a simple application of outer join.

Here I include the test data in the with clause. You don't need it - you have the actual tables. Remove the with clause, and inspect the main query and change the table and column names as needed.

with
  table_1 (id, name) as (
    select 24, 'Boris'     from dual union all
    select 25, 'Dominic'   from dual union all
    select 26, 'Rishi'     from dual union all
    select 27, 'Elizabeth' from dual union all
    select 28, 'Ben'       from dual
  )
, table_2 (id, description) as (
    select 27, 'Apple'  from dual union all
    select 27, 'Orange' from dual union all
    select 27, 'Pear'   from dual union all
    select 26, 'Apple'  from dual union all
    select 26, 'Pear'   from dual union all
    select 25, 'Pear'   from dual union all
    select 24, 'Orange' from dual
  )
select t1.name, t2.description
from   table_1 t1 left outer join table_2 t2 
                  on t2.id = t1.id and t2.description = 'Apple'
order  by t1.id
;


NAME      DESCRIPTION
--------- -----------
Boris           
Dominic         
Rishi     Apple 
Elizabeth Apple 
Ben