'SQL: Return boolean if there is at least 1 record between 2 tables

I have the following 2 tables:

Table 1 priceList

ID CurrencyID
3 DF10CCE

Table 2 priceListItems

ID priceListID Product
1 3 DESK

I would like to write a statement in SQL to return a boolean (0 or 1) if the priceList has Items in, comparing with priceListItems based on their ID columns (For table A: ID = 3, and for Table B: priceListID = 3 )

How I can achieve that?

sqlsql-server


Solution 1:[1]

A simple left join and a case can fix this

select pl.*, 
       convert(bit, case when pli.ID is null then 0 else 1 end) as HasItems
from   PriceList pl 
  left join PriceListItem pli on pl.ID = pli.priceListID

Note that there is no boolean type in sql server, the closest is the bit type that can only be 0/1 and many software will show it as false/true

Click on this link to see the query working

The result is

id currencyid HasItems
3 DF10DDE True
4 blablabla False

Solution 2:[2]

Try

WITH CTE AS
(
    SELECT a.ID FROM priceList a inner JOIN priceListItems b
    ON a.ID = b.priceListID
)
SELECT 
    CASE WHEN EXISTS (SELECT 1 from CTE) then 1 ELSE 0 END as bool

Solution 3:[3]

You can use outer joins :

select pl.*, 
      (case when pli.priceListID is not null then 1 else 0 end) as flag
from  priceList pl left join
      priceListItems pli
      on pli.priceListID = pl.id

Solution 4:[4]

your data

declare @priceList table (
   ID         int NOT NULL 
  ,CurrencyID VARCHAR(70) NOT NULL
);
INSERT INTO @priceList
(ID,CurrencyID) VALUES 
(3,'DF10CCE');


declare @priceListItems table (
   ID          int NOT NULL 
  ,priceListID int NOT NULL
  ,Product     VARCHAR(40) NOT NULL
);
INSERT INTO @priceListItems
(ID,priceListID,Product) VALUES 
(1,3,'DESK');

Use full join for distinguish the existence.

SELECT Iif(pl.id IS NULL, 0, 1)
FROM   @priceListItems pli
       FULL JOIN  @priceList pl
              ON pl.id = pli.pricelistid  
            --  where pl.id =3 --where condition

Solution 5:[5]

Note that a simple left join is not sufficient because (I assume) query is supposed to return only one record per priceList record. Try:

select
    pl.ID,
    case
        when pli.priceListID is null then 0
        else 1
    end as HasItems
from
    priceList as pl
    left join (select distinct priceListID from priceListItems) as pli on pli.priceListID = pl.ID
;

Solution 6:[6]

select coalesce(max(1), 0)
from priceList p inner join priceListItems pi on pi.priceListID = p.ID
where p.ID = X

The inner join may even be redundant given the relationship.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 JohanB
Solution 3 Yogesh Sharma
Solution 4
Solution 5
Solution 6 shawnt00

Related Questions