Welcome Guest, Not a member yet? Register   Sign In
SQL Sub-query
#1

[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




Theme © iAndrew 2016 - Forum software by © MyBB