mySQL query don't return me the expected results

Question

I have a problem with this query:

SELECT *,
GROUP_CONCAT(COALESCE(STX_Amount, "0")) AS ApplicableTaxesRate,
GROUP_CONCAT(COALESCE(STX_TaxeName, "0")) AS ApplicableTaxesName
FROM ___BillableDatas
JOIN ___Invoices ON ___BillableDatas.BIL_BookingId=___Invoices.IVC_BookingId 
JOIN ___Bookings ON ___BillableDatas.BIL_BookingId=___Bookings.BOO_Id 
JOIN ___Inventory ON ___BillableDatas.BIL_Item=___Inventory.INV_Id 
LEFT JOIN ___SalesTaxes ON FIND_IN_SET(STX_Id, BIL_ApplicableTaxes) > 0 
WHERE BIL_HotelId='AAA00' AND IVC_Id=14 AND BIL_Id IN (IVC_Elements)
ORDER BY BIL_Id ASC

From this sqlFiddle (http://sqlfiddle.com/#!9/b2016) and with my actual datas, this query returns me only one row whereas it should returns me 3 (3 because IVC_Elements contains 3 different ids).

--

The desired output should be the following:

|----------|----------|------------|----------|----------|--------------|--------------------|---------------------|
| BIL_Id   | IVC_Id   | BIL_Date   | BIL_Rate | BIL_Type | BIL_Quantity | INV_Name           | BIL_ApplicableTaxes |
|----------|----------|------------|----------|----------|--------------|--------------------|---------------------|
| 31       | 14       | 2017-03-15 | 130.00   | Night    | 1            |                    |                     |
| 32       | 14       | 2017-03-13 | 130.00   | Night    | 1            |                    |                     |
| 33       | 14       | 2017-03-17 | 130.00   | Night    | 1            |                    |                     |
| 45       | 14       | 2017-11-01 | 100.00   | Item     | 5            | Lit supplémentaire | 11                  |
| 50       | 14       | 2017-03-16 |  12.00   | Item     | 2            | Petit-déjeuner     |                     |
|----------|----------|------------|----------|----------|--------------|--------------------|---------------------|

Any help please ?

Answer 1

Why IN on WHERE clause does not work

Because you are storing a coma delimited string on IVC_Elements and, I'm sorry, but you cannot use it on this way:

 WHERE FIELD IN (FIELD_WITH_COMA_DELIMITED_VALUES); 

IN clause requires a list of values

If fact, if you use current value '31,32,33' it returns ID=31 that is the only one it can evaluate, however try to change it by '32,31,33' and you'll obtain row ID=32.

How can you solve it: Change it by:

FIND_IN_SET(BIL_Id, IVC_Elements) > 0

Change your query on this way:

SELECT    BIL_Id, IVC_Id, DATE_FORMAT(BIL_Date, "%Y-%m-%d") BIL_Date, BIL_Rate, BIL_Type, 
          BIL_Quantity, COALESCE(INV_Name, '') INV_Name, BIL_ApplicableTaxes
FROM      ___BillableDatas
JOIN      ___Invoices  
ON        ___BillableDatas.BIL_BookingId = ___Invoices.IVC_BookingId
JOIN      ___Bookings  
ON        ___BillableDatas.BIL_BookingId = ___Bookings.BOO_Id
LEFT JOIN ___Inventory 
ON        ___BillableDatas.BIL_Item = ___Inventory.INV_Id
LEFT JOIN ___SalesTaxes 
ON        FIND_IN_SET(BIL_ApplicableTaxes, STX_Id) > 0
WHERE     BIL_HotelId='AAA00' 
AND       IVC_Id=14
AND       FIND_IN_SET(BIL_Id, IVC_Elements) > 0 
ORDER BY  BIL_Id ASC
;

This is the result:

+--------+--------+------------+----------+----------+--------------+----------+---------------------+
| BIL_Id | IVC_Id | BIL_Date   | BIL_Rate | BIL_Type | BIL_Quantity | INV_Name | BIL_ApplicableTaxes |
+--------+--------+------------+----------+----------+--------------+----------+---------------------+
| 31     | 14     | 2017-03-15 | 130,00   | Night    | 1            |          |                     |
+--------+--------+------------+----------+----------+--------------+----------+---------------------+
| 32     | 14     | 2017-03-16 | 130,00   | Night    | 1            |          |                     |
+--------+--------+------------+----------+----------+--------------+----------+---------------------+
| 33     | 14     | 2017-03-17 | 130,00   | Night    | 1            |          |                     |
+--------+--------+------------+----------+----------+--------------+----------+---------------------+

Check it here: http://rextester.com/BMX51701