SQL Sub-query - Printable Version

SQL Sub-query - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: SQL Sub-query (/showthread.php?tid=29560)

SQL Sub-query - El Forum - 04-13-2010

[eluser]theshiftexchange[/eluser]
Hi guys,

I'm trying to write an SQL sub-query. I cant work out how to write it (the SQL logic, not the bit to make it work in CI). I've got two separate queries that achieve what I want, but I'm trying to make them into one.

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 run a loop in an array, and 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 (or blank or 0 or something if none)

Thanks in advance