CODEWITHSUNDEEP
sql
giaco
giaco

Reputation: 13

If exists select column from another table

I need help solving this problem. I have two tables:

Table 1 

MonthYear   Code    Value
jan/18      4169    50
jan/18      4102    95

Table 2 

Date    Code    Value
jan/18  4102    30
jan/18  4102    10
jan/18  4102    15
jan/18  4102    40

I need to make a query that returns the value field of table 1 if there is no code in table 2 or that returns the value field of table 2 if there is code in table 2.

For example:

MonthYear   Code    Value
jan/18      4169    50
jan/18      4102    30
jan/18      4102    10
jan/18      4102    15
jan/18      4102    40

Upvotes: 0

Views: 472

Answers (3)

Barbaros Özhan
Barbaros Özhan

Reputation: 65105

You may use coalesce :

select t1.MonthYear, 
       coalesce(t2.Code,t1.Code) as Code, 
       coalesce(t2.value,t1.value) as value
  from table2 t2
  right outer join table1 t1
    on ( t2.Code = t1.Code );

SQL Fiddle Demo

P.S. I chose PostGreSQL as DB for the Demo, you may use this SQL also for Oracle, SQL-Server or MySQL.

Actually, you'd better replacing keyword right with full in the SQL as :

select coalesce(t2.MonthYear,t1.MonthYear) as MonthYear, 
       coalesce(t2.Code,t1.Code) as Code, 
       coalesce(t2.value,t1.value) as value
  from table2 t2
  full outer join table1 t1
    on ( t2.Code = t1.Code );

SQL Fiddle Demo 2

except for MySQL, since there occurs an alternative case in which the code exists in Table2 but not Table1, full outer join provides the solution( special thanks to @Error_2646 ).

Upvotes: 1

Vishnu Kunchur
Vishnu Kunchur

Reputation: 1726

SELECT * FROM table2
UNION ALL
SELECT * FROM table1 WHERE Code NOT IN (SELECT Code FROM table2)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269443

I would approach this as:

select t2.date, t2.code, t2.value
from table2 t2
union all
select t1.date, t1.code, t1.value
from table2 t1
where not exists (select 1 from table2 t2 where t1.date = t2.date and t1.code = t2.code);

Upvotes: 1

Related Questions