CODEWITHSUNDEEP
sqlpostgresql
Rahul Iyer
Rahul Iyer

Reputation: 21025

How to return a result as a column value if a row exists or not?

I have two tables table1 and table2.

table1 - tID,title,description

table2 - sID,tID,title,description

table1(t1) always contains values. In table2 (t2), if there is a row present, the tID (Foreign key) will always match a tID in t1(primary key).

I want to create a select statement where I select all columns from t1, and create an additional column "ex" with a true or false value, depending on whether there is a record in t2, where tID matches...

Select t1.*, (some code) as ex from table1 t1,table2 t2;

Understand ? For example if Select t1.*, t2.* from table1 t1, table2 t2 where t1.tID = t2.tID returned any rows, ex similarly should be true... how do I do that ?

EDIT: No column can be null

EDIT: Forgot to mention, I want the select statement to match only where sID = someIdValue that I specify....

Upvotes: 0

Views: 1436

Answers (4)

wildplasser
wildplasser

Reputation: 44250

EXISTS(subquery) yields a boolean value which you can use directly:

SELECT t1.*
        , EXISTS(SELECT13 FROM table2 t2 WHERE t2.tID = t1.tID) AS does_exist
FROM table1 t1
        ;

  • This will produce all the rows from t1, plus a flag indicating that one or more corresponding rows exist in t2.

  • If you only want the t1 records that have matching rows in t2, move the exists() to the where clause:

SELECT t1.*
FROM table1 t1
WHERE EXISTS(SELECT13 FROM table2 t2 WHERE t2.tID = t1.tID) 
        ;

Upvotes: 0

Radim Bača
Radim Bača

Reputation: 10711

Use LEFT JOIN (outer join) and then check for the existence of tID in table2 by using of the IS NULL expression

Select t1.*, t2.tid is not null as ex 
from table1 t1
left join table2 t2 on t1.tid = t2.tid

demo

EDIT: "What if I wanted only the values where sID matches some value ?"

Select t1.*, t2.tid is not null as ex 
from table1 t1
left join table2 t2 on t1.tid = t2.tid and t2.sid = <some_value>

Upvotes: 1

JustMe
JustMe

Reputation: 2383

Try this

select *, coalesce(tID = true, false) = true as is_exists from table1 t1
left join table2 t2 using (tid)

Upvotes: 1

Amir
Amir

Reputation: 2052

Try below one if this work for you.

 declare @T1 as Table 
(
tId int,
title varchar(25),
description varchar(50)
)

declare @T2 as Table 
(
sid int,
tId int,
title varchar(25),
description varchar(50)
)
insert into @t1 (tId,title,description) values(1,'abc','test')
insert into @t1 (tId,title,description) values(2,'abc','test')
insert into @t1 (tId,title,description) values(3,'abc','test')
insert into @t1 (tId,title,description) values(4,'abc','test')
insert into @t1 (tId,title,description) values(5,'abc','test')

insert into @t2 (tId,title,description) values(10,'abc','test')
insert into @t2 (tId,title,description) values(2,'abc','test')
insert into @t2 (tId,title,description) values(3,'abc','test')
insert into @t2 (tId,title,description) values(14,'abc','test')
insert into @t2 (tId,title,description) values(5,'abc','test')

Select distinct t1.*, 
   case when t2.tid is null then 'False' else 'True' end as ex from @T1 t1    
   left join @T2 t2 
   on t1.tId = t2.tId

Output

1   abc test    False
2   abc test    True
3   abc test    True
4   abc test    False
5   abc test    True

Upvotes: 1

Related Questions