php/mysql help

[c_pattle]

I have two tables called "hours" and "holiday".  One records employees hours and the other records any holidays they have booked.  What I want to do is to have a mysql query that will select all of the records in the "hours" table for "jbloggs" and then select from the "holiday" table any holidays that "jbloggs" has that month.  If I was to do this as two separate sql queries it would be,

 

select * from hours where username="jblogg"

 

and

 

SELECT number_of_days FROM holiday WHERE username="jbloggs" AND holiday.date_start >= '2011-07-01' and holiday.date_start < date_add('2011-07-01',interval 1 month)

 

So the result set I want is to have all of the hours printed out and on each row the number of days holiday for that month which in this case would be 2.  I tried using a join but I kept getting lots of rows in the hours table repeated.  Is there anyway to do it so that row won't be repeated?

 

Below are what the tables might look like. 

 

Hours table

 

username

date

clock_in

clock_out

jbloggs

2011-07-01

08:53:15

17:00:04

jbloggs

2011-07-02

08:52:42

17:00:09

 

Holiday table

username

date_start

number_of_days

jbloggs

2011-07-25

2

jbloggs

2011-08-11

5

 

Thanks for any help!

 

[requinix]

The query you say you want doesn't quite make sense. What kind of output are you looking for?

[c_pattle]

So for example in this example the output I'd want would be

 

username

date

clock in

clock out

number of days (holiday)

jbloggs

2011-07-01

08:53:15

17:00:04

2

jbloggs

2011-07-02

08:52:42

17:00:09

2

 

The results would show all of the records from the "hours" table with the number of days holiday from the holiday table that they have that month. 

[requinix]

So you'd really want to repeat that "2" for every single day of that month?

 

Since there could be multiple holidays per month, you could use a subquery to SUM them together:

SELECT username, DATE_FORMAT("%Y-%m") AS month, SUM(number_of_days) AS number_of_days FROM holiday GROUP BY month

Stick that into a join and you get

LEFT JOIN (
    SELECT username, DATE_FORMAT("%Y-%m") AS month, SUM(number_of_days) AS number_of_days FROM holiday GROUP BY month
) AS holiday ON DATE_FORMAT("%Y-%m", hours.date) = holiday.month

Since there could be no holidays for a month, it's an outer join. Use an IFNULL to get 0 for such months.

SELECT ..., IFNULL(holiday.number_of_days, 0) AS holidays...