MySQL query help

[c_pattle]

I have a table called "staff" which has a list of employees details.  I also have a table called hours which stores employee hours. 

 

What I want to do is the have a query that selects all the fields from the staff table such as

 

SELECT * FROM staff WHERE username="jbloggs"

 

and also to select the number of times this employee has called in sick such as

 

SELECT COUNT(status) FROM hours WHERE status="sick" and username="jbloggs"

 

Is there an easy way to merge these two sql queries into one?

 

Thanks for any help

[AyKay47]

look into using a JOIN

[fenway]

SELECT

s.*

, COUNT(h.status)

FROM staff AS s

LEFT JOIN hours AS h ON ( h.username=s.username AND h.status="sick" )

WHERE s.username="jbloggs"

 

[c_pattle]

Thanks for your help.  I understand it now.  Sorry another question.  Is it also possible to perform a count on the same field but using a different condition?  What I also want to do is to count the number of times h.status="late". 

 

Would I be able to do this in the same query because I'm already performing a count on this field?

[Muddy_Funster]

SELECT
s.*
, COUNT(h.status)
FROM staff AS s
LEFT JOIN hours AS h ON ( h.username=s.username AND (h.status="sick" OR h.status = "Late") )
WHERE s.username="jbloggs"

GROUP BY h.status

Should work....I think

[c_pattle]

Thanks.  Yeah that should work but what I want to do is to perform a different count for each criteria.  So the final result set will have seperate count for when the status is "sick" and a seperate one where the status is "late".  Is this possible?

 

Thanks

[Muddy_Funster]

did you try

SELECT
s.*
, COUNT(h.status)
FROM staff AS s
LEFT JOIN hours AS h ON ( h.username=s.username AND (h.status="sick" OR h.status = "Late") )
WHERE s.username="jbloggs"

GROUP BY h.status

 

I messed up the tags on the last post.

[kickstart]

Hi

 

Or, if you want each person to be on a line (so not a separate row for sick and late):-

 

SELECT s.*, COUNT(h.status), COUNT(l.status)
FROM staff AS s
LEFT JOIN hours AS h ON ( h.username=s.username AND h.status="sick")
LEFT JOIN hours AS l ON ( l.username=s.username AND  h.status = "Late" )
WHERE s.username="jbloggs"
GROUP BY s.username

 

All the best

 

Keith

[fenway]

Oops, for the GROUP BY -- thanks guys.

[c_pattle]

Sorry Muddy_Funster I missed the group by on your first post.  Thanks for the solution.  And also thanks Keith!