CODEWITHSUNDEEP
sql-serverif-statement
anavaras lamurep
anavaras lamurep

Reputation: 1493

Use of if exists( ) in select statement

I need to fetch a column from table2, if row exists, if not return it as null.

If i use ,case when it fetches only matched rows between table1 and table2.

If i use left outer join it fetches all the rows from table1 even though condition table1.code='A'

So i need ,some thing like this.

select table1.id,
if(row exist in table2 for query(table2.relation_type_id=55  and table1.id=table2.related_id)
then
return table2.parent_id
else
null
as parent_id,
table1.description,
from table1,table2 where table1.code='A'

Upvotes: 0

Views: 227

Answers (2)

I_am_Batman
I_am_Batman

Reputation: 915

SELECT table1.id, table2.parent_id as parent_id 
FROM table1 
LEFT OUTER JOIN table2 ON (table1.id = table2.related_id) 
WHERE table1.code = 'A';

EDIT based on comment :

  SELECT table1.id, sub.parent_id as parent_id 
    FROM table1 
    LEFT OUTER JOIN (select parent_id,related_id from table2 where relation_type_id =55) sub ON (table1.id = sub.related_id) 
    WHERE table1.code = 'A';

Upvotes: 1

navigator
navigator

Reputation: 1708

Have you tried a sub-query?

select table1.id,
(select table2.parent_id from table2 where table2.relation_type_id=55 and table2.related_id=table1.id) parent_id,
table1.description,
from table1 where table1.code='A'

Upvotes: 0

Related Questions