SQL : Find if table's 'A' ID exists to table's 'B' ID-CodePudding

I have 2 tables. Table A is priceList that contains 2 columns. ID and currency_id . Table B is priceListItems that contains 3 columns. Product priceListID ProductID.

For Example priceList

ID	CurrencyID
3	DF10CCE

And 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 , and for Table B: priceListID )

So if the priceList's ID exists in priceListItems's priceListID, the result should be true.

How i can achieve that?

Is the follwing correct?

SELECT priceList.id, 
       IF(priceListItems.id IS NULL, FALSE, TRUE) as priceListItems
FROM priceList
LEFT JOIN priceListItems ON (priceList.id = priceListItems.id)

CodePudding user response：

SELECT id,
       EXISTS ( SELECT NULL
                FROM priceListItems
                WHERE priceList.id = priceListItems.priceListID )
FROM priceList;

CodePudding user response：

priceList.id = priceListItems.id this test is incorrect and should be priceList.id = priceListItems.pricelistid

and IF(priceListItems.id IS NULL, FALSE, TRUE) is dubious surely if null then true makes more sense..

CodePudding user response：

The if() function can get the job nicely done. Assuming ID 4 from the pricelist has two rows in the pricelistitems and ID 5 from the pricelist has no entry in the pricelistitems, we can try:

insert priceList values(3,'DF10CCE'),(4,'DF11223'),(5,'DD11225');
insert priceListItems values(1,3,'desk'),(2,4,'pp'),(3,4,'ss');
select id, if((select count(*) from priceListItems where pricelistid=pl.id),1,0) from priceList pl;

The correlated subquery (select count(*) from priceListItems where pricelistid=pl.id) returns an int which is greater or equal to 0, which can be used directly as a boolean in the if() function to determine which value should be returned.