CODEWITHSUNDEEP
mysqlsqlnotin
Faiyaz Md Abdul
Faiyaz Md Abdul

Reputation: 667

mysql - not in statement

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 .

Upvotes: 0

Views: 53

Answers (3)

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48177

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 |

Upvotes: 2

Tuan Duong
Tuan Duong

Reputation: 515

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

Upvotes: 0

Avani
Avani

Reputation: 215

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;

Upvotes: 0

Related Questions