marcvander marcvander - 1 year ago 79
SQL Question

MYSQL IF (SELECT) query returns more than one row

I'm trying to implement an IF (SELECT) statement in an SQL query. The query looks like this so far:

SELECT *, IFNULL(duree_corrigee,duree) as
duree_final, IF((SELECT COUNT(*) FROM pointage_interval WHERE panier=1
AND pointage_employe_id = 'FH'
AND semaine = 23 GROUP BY date)>=1,
1,0) as panier_final FROM pointage_interval P
JOIN
employe E ON P.pointage_employe_id
= E.employe_id JOIN chantier C
ON C.chantier_id=P.pointage_chantier_id
WHERE (pointage_employe_id = 'FH'
AND semaine = 23)


When I run it, it returns me: #1242 - Subquery returns more than 1 row

Basically I can have many rows with the same date. One column is "panier", and can be either 1 or 0. I would like to create a new column panier_final, which takes the value 1 or 0 based on if there is a least one column with value of panier = 1 for the same day. So all the rows with this same date will get the same value 1 or 0 for panier_final.

If I run the query without the IF (SELECT), as this:

SELECT *, IFNULL(duree_corrigee,duree) as
duree_final FROM pointage_interval P
JOIN
employe E ON P.pointage_employe_id
= E.employe_id JOIN chantier C
ON C.chantier_id=P.pointage_chantier_id
WHERE (pointage_employe_id = 'FH'
AND semaine = 23)


I get this output:

enter image description here

As you can see, there are two rows for the same day, 2017-06-07. So I would like my IF (SELECT) query to create a new column (panier_final) for each row, the value of this new column being the same for each same date's rows. In this case each row would get a new column panier_final, the value being 1 since that for each given date, the values panier are equal or superior to 1

Ali Ali
Answer Source

add this condition to your subquery : date = P.date

your subquery will be like this :

SELECT COUNT(*) FROM pointage_interval WHERE panier=1 
AND pointage_employe_id = 'FH' AND date = P.date
AND semaine = 23 GROUP BY date

and i think you could remove GROUP BY date

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download