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

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?

CodePudding user response：

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

CodePudding user response：

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

CodePudding user response：

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

CodePudding user response：

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

CodePudding user response：

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
;

CodePudding user response：

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.