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

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)
mysqlsql


Solution 1:[1]

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

Solution 2:[2]

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

Solution 3:[3]

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.

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 Akina
Solution 2 P.Salmon
Solution 3 blabla_bingo

Related Questions