CODEWITHSUNDEEP
mysqlsqlif-statement
marcvander
marcvander

Reputation: 629

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

Upvotes: 0

Views: 2040

Answers (1)

Ali Faris
Ali Faris

Reputation: 18592

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

Upvotes: 1

Related Questions