CODEWITHSUNDEEP
sqljoin
capdragon
capdragon

Reputation: 14899

sql join which tells me if ID exists in other table

I have 2 tables:

 A      B
 --    ----
 ID    FKID
 --    ----
 1      3
 2      3   
 3      4
 4      4

I need a select statement which shows me all of A with a field that tells me if table B has any ids that match that ID.

Desired Result
-----------
 ID | hasB
-----------
 1    no
 2    no    
 3    yes
 4    yes

Upvotes: 6

Views: 7330

Answers (6)

Michael Fredrickson
Michael Fredrickson

Reputation: 37378

SELECT DISTINCT
    a.ID,
    CASE WHEN b.FKID IS NULL THEN 'no' ELSE 'yes' END AS hasB
FROM
    tableA a LEFT JOIN
    tableB b ON a.ID = b.FKID

Upvotes: 3

Chris Werner
Chris Werner

Reputation: 1386

Either of these should do: select distinct a.id,(case when b.fkid is null then 0 else 1 end) as hasb from tablea a left join tableb b on a.id=b.fkid

select a.id,(case when exists(select * from tableb where a.id=fkid) then 1 else 0 end) as hasb from tablea a

Upvotes: 0

kba
kba

Reputation: 19466

Using the following query

SELECT DISTINCT ID,IF(FKID,'yes','no') AS hasB
FROM A LEFT JOIN B ON A.ID = B.FKID;

You'll get

+------+------+
| ID   | hasB |
+------+------+
|    1 | no   |
|    2 | no   |
|    3 | yes  |
|    4 | yes  |
+------+------+
4 rows in set (0.07 sec)

Upvotes: 0

Alpha01
Alpha01

Reputation: 856

SELECT ID, "no" as hasB from a where not(id in (select fkid from b) )

union

SELECT ID, "yes" as hasB from a where id in (select fkid from b)

Upvotes: 0

Martin Smith
Martin Smith

Reputation: 452957

In SQL Server this would be the most efficient way rather than doing an OUTER JOIN then removing the duplicates with DISTINCT. Not sure for postgres, you'd need to check the plans.

SELECT ID,
       CASE
         WHEN EXISTS (SELECT *
                      FROM   B
                      WHERE  B.FKID = A.ID) THEN 'yes'
         ELSE 'no'
       END AS hasB
FROM   A

Upvotes: 12

Chris W
Chris W

Reputation: 3314

Something like this should work out for you depending on what the DB platform actually is. You may need to swap the case for an IIF on something lovely like MS Access.

select A.ID, case when B.FKID IS NULL then 'no' else 'yes' end as hasB from A left join B on A.ID = B.FKID

Upvotes: 1

Related Questions