query help

[farahZ]

i'm trying to view in a table:

 

daytime --- meal --- calories

 

the table in the database has (fooddiary)

ID--Date---DayTime---Food--Calories

 

and table: food

FID---FoodName---Size---Calories

 

i'm not able to show the food a person ate for each meal !!

DayTime and Sum of Calories were working fine,, now trying to add the food a user ate corresponding to the day time.. please help me

 

 

SELECT fe.DayTime, SUM(f.Calories) as Calories, (select FoodName from fooddiary where DayTime=fe.DayTime) as meal

FROM fooddiary fe

INNER JOIN food f USING (Food)

WHERE fe.ID= 111 And fe.Date='$date'

GROUP BY fe.DayTime

[Jessica]

Don't do it as a sub-select, do it as a regular join.

[farahZ]

dont wanna mess up the daytime and calories.. they are working fine here!

[farahZ]

can it be using like this?

 

 

 
SELECT fe.DayTime, SUM(f.Calories) as Calories, GROUP_CONCAT(select Food from fooddiary where DayTime=fe.DayTime and Date=2013-05-17)
FROM fooddiary fe
INNER JOIN food f USING (Food)
WHERE fe.ID= 111 And fe.Date=2013-05-17
GROUP BY fe.DayTime

[jazzman1]

 

 

can it be using like this?

 

No, it's wrong.

 

Start from here.