0
table 1 
id  name    value activ
1    abc    5      1
2    def    6      1
3    ghi    10     0
4    jkl    15     1


table 2 
id   name   value  table1_id
1    abc    100     1
2    jkl    200     4

i want to return all records from table 1 where active = 1 and the records from table 2 where table1_id refers the id of table 1 and thus skipping the record of table 1 which matches the table1_id in table 2..

the output must me something like this 

name  value 
 abc    100
 def     6
 jkl     200

i trie something like this .. 

select   s.name,
         s.value  
from table1 as s 
where s.active =1 and 
      s.id NOT `IN (select d1.table1_id 
                    from table2 as d1 
                    where d1.table1_id = s.id) 
union 
select d.id,
     d.name,
     d.value 
from table2 as d`

it always returns all records of table 2 . i am unable use the where condition at the end of the statement like 'where d.table1_id = s.id' . It says s.id is unknown .

Jaydip Jadhav
  • 12,179
  • 6
  • 24
  • 40
Faiyaz Md Abdul
  • 546
  • 5
  • 14
  • 29
  • Check your parentheses. Especially, check whether you meant to have `UNION` **outside** of your sub-query. – PM 77-1 Mar 16 '16 at 18:09

3 Answers3

2

SQL Fiddle Demo

SELECT T1.name, 
       COALESCE(T2.value, T1.value) as value
FROM Table1 as T1
LEFT JOIN Table2 as T2
       ON T1.id = T2.table1_id
WHERE T1.active = 1

OUTPUT

| name | value |
|------|-------|
|  abc |   100 |
|  jkl |   200 |
|  def |     6 |
Juan Carlos Oropeza
  • 47,252
  • 12
  • 78
  • 118
0
SELECT a.name,
  NVL(b.value, a.value)
FROM table1 a
LEFT OUTER JOIN table2 b
ON a.id      =b.table1_id
WHERE a.activ=1;
Avani
  • 205
  • 1
  • 6
0

You can use COALESCE. 

SELECT table_1.name, COALESCE(table_2.value, table_1.value) as value
FROM table_1
LEFT JOIN table_2
   ON table_1.id = table_2.id
WHERE table_1.active = 1
Tuan Duong
  • 515
  • 3
  • 7