Help writing a SQL subquery

[eluser]theshiftexchange[/eluser]
Hey guys,

I know this needs to be a SQL subquery - but after looking/googling/trying I cant work out the logical answer to this "seemingly" simple subquery.

Table A has a list of overtime worked by users
Table B is a list of users

I need the total overtime worked by each user (first SQL statement below) included in the user list I'm generating (second SQL statement)

Table A

Code:

SELECT SUM(`shift_length`) AS shift_length FROM (`overtime_assigned`)
WHERE `user_ID` = '.$user_ID

Table B

Code:

SELECT * FROM (`overtime_list`)
JOIN `users` ON `users`.`user_ID` = `overtime_list`.`user_ID`
JOIN `stations` ON `users`.`station_ID` = `stations`.`station_ID`
JOIN `postcode_db` ON `users`.`postcode_ID` = `postcode_db`.`postcode_ID`
WHERE `date` = '.$date.' AND `type` = '.$type.' AND `day` = '.$day.'
GROUP BY `postcode_db`.`postcode_ID`

Anyone able to help shed light on this? Thanks in advance.

[eluser]danmontgomery[/eluser]

Code:

SELECT SUM(`shift_length`) AS shift_length FROM (`overtime_assigned`)
WHERE user_ID IN ( SELECT users.user_ID FROM overtime_list
JOIN `users` ON `users`.`user_ID` = `overtime_list`.`user_ID`
JOIN `stations` ON `users`.`station_ID` = `stations`.`station_ID`
JOIN `postcode_db` ON `users`.`postcode_ID` = `postcode_db`.`postcode_ID`
WHERE `date` = '.$date.' AND `type` = '.$type.' AND `day` = '.$day.'
GROUP BY `postcode_db`.`postcode_ID`)
GROUP BY user_id

Should work

[eluser]theshiftexchange[/eluser]
[quote author="noctrum" date="1270670516"]

Code:

SELECT SUM(`shift_length`) AS shift_length FROM (`overtime_assigned`)
WHERE user_ID IN ( SELECT users.user_ID FROM overtime_list
JOIN `users` ON `users`.`user_ID` = `overtime_list`.`user_ID`
JOIN `stations` ON `users`.`station_ID` = `stations`.`station_ID`
JOIN `postcode_db` ON `users`.`postcode_ID` = `postcode_db`.`postcode_ID`
WHERE `date` = '.$date.' AND `type` = '.$type.' AND `day` = '.$day.'
GROUP BY `postcode_db`.`postcode_ID`)
GROUP BY user_id

Should work[/quote]

Thanks Noctrum - its close; its correctly giving me all the user overtime totals - but I need it to also return the users table results - such as first_name etc. Currently the array is only the user_ID and the overtime total?

I had a quick try - but everything gives me errors

[eluser]danmontgomery[/eluser]
Ah... You didn't say that.

Code:

SELECT users.*, SUM(`shift_length`) AS shift_length FROM (`overtime_assigned`)
JOIN users ON users.user_ID = overtime_assigned.user_ID
WHERE users.user_ID IN ( SELECT users.user_ID FROM overtime_list
JOIN `users` ON `users`.`user_ID` = `overtime_list`.`user_ID`
JOIN `stations` ON `users`.`station_ID` = `stations`.`station_ID`
JOIN `postcode_db` ON `users`.`postcode_ID` = `postcode_db`.`postcode_ID`
WHERE `date` = '.$date.' AND `type` = '.$type.' AND `day` = '.$day.'
GROUP BY `postcode_db`.`postcode_ID`)
GROUP BY users.user_ID

Is the users table necessary in the subquery? If at all possible I would eliminate that join (you can select the user_ID from overtime_list table, for example)

[eluser]theshiftexchange[/eluser]
Thanks noctrum your the best!!!!!!!

In regards to your question; it was confusing me, then I realised the sub-query is back to front (my fault - I didnt explain it correctly).

The JOINS are for the main bit, and the subquery is the count, rather than the other way around. So the end result will be all the data from the users table (with JOINS from stations, postcode etc) and the result of the subquery of the overtime count.

I'll muck around with it - now I have all the syntax its just a matter of switching it around I think.

Cheers!!!!!!!!!!!!!!

[eluser]theshiftexchange[/eluser]
After hours of mucking around - I still cant get it

I'll simplify the SQL statement:

This is my main select statement - it grabs all the users that are available on a particular day.

Code:

SELECT * FROM overtime_list
JOIN users ON users.user_ID = overtime_list.user_ID
WHERE overtime_list.date = '$date'

Now for each of those users, I want it to check another table to see how much (if any) overtime they have done up to now:

Code:

SELECT SUM(`shift_length`) AS overtime_total FROM (`overtime_assigned`)
WHERE `user_ID` = '.$user_ID

The "WHERE `user_ID` = '.$user_ID " gets me the sum for that individual user

So the idea is all the users on a particular day are always returned, and any that have previously done overtime will have their total included as "overtime_total" on the array

Thanks in advance