Sql join that tells me if an identifier exists in another table

Question

I have 2 tables:

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

I need a select statement that shows me all A with a field that tells me if table B has any identifiers matching that identifier.

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

Answer 1

In SQL Server, this would be the most efficient way, rather than executing OUTER JOINand then removing duplicates using DISTINCT. Not sure if for postgres you will need to check plans.

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

Answer 2

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

Answer 3

- , . , IIF - , 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

Answer 4

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

union

SELECT ID, yes, like hasB, from if identifier (select fkid from b)

Answer 5

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)

Answer 6

Any of them 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