serviceco
asked on
MySQL Subquery
I am relatively new to attempting to understand subqueries and how much potential power they have. I have found quite a few uses already, and think my current information can be attained via subquery but am not positive.
QUERY 1:
This may return several records
jobid phaseid submitted projection
940848 81303 9/1/2015 10.00
940848 91203 9/2/2015 8.00
So I could output the query and loop back through with a series of queries that each search using the results of the first query:
However, I am convinced that there is a means to tell the second query to use the parameters from the first query if I better understood the power of subqueries.
QUERY 1:
SELECT
jobcost_projection.jobid,
jobcost_projection.phaseid,
Date(jobcost_projection.submitted) As submitted,
jobcost_projection.projection
FROM
jobcost_projection
WHERE
jobcost_projection.jobid = 940848 This may return several records
jobid phaseid submitted projection
940848 81303 9/1/2015 10.00
940848 91203 9/2/2015 8.00
So I could output the query and loop back through with a series of queries that each search using the results of the first query:
SELECT
timecards.Jobid,
timecards.Phaseid,
Sum(timecards.Time)
FROM
timecards
WHERE
timecards.Workdate > '2015-09-01'
AND timecards.Phaseid = '81303'
AND timecards.Jobid = '940848'
GROUP BY
timecards.Jobid,
timecards.PhaseidHowever, I am convinced that there is a means to tell the second query to use the parameters from the first query if I better understood the power of subqueries.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Surrano
Ryan's right but make sure to use the alias "a" instead of table name proper in the group by clause as well.
ASKER
Thank you! That was definitely the help I needed. I did not understand I could pass the parameter in the ON statement.
tks for reminder by Surrano.
the proper SQL should be like this:
@serviceco
for the ON clause, it's needed for JOIN statement as the condition expression. for more info, you can refer to:
13.2.8.2 JOIN Syntax
https://dev.mysql.com/doc/refman/5.0/en/join.html
the proper SQL should be like this:
SELECT
a.Jobid,
a.Phaseid,
Sum(a.Time)
FROM
timecards a
inner join
(
SELECT
jobcost_projection.jobid,
jobcost_projection.phaseid,
Date(jobcost_projection.submitted) As submitted,
jobcost_projection.projection
FROM
jobcost_projection
) b
on
a.Phaseid = b.Phaseid
AND a.Jobid = b.Jobid
where a.jobid = 940848 and a.Workdate >= b.submitted
GROUP BY
a.Jobid,
a.Phaseid @serviceco
for the ON clause, it's needed for JOIN statement as the condition expression. for more info, you can refer to:
13.2.8.2 JOIN Syntax
https://dev.mysql.com/doc/refman/5.0/en/join.html
Personally I prefer the form without the JOIN keyword:
Select... From a, b where a.x=b.y...
Select... From a, b where a.x=b.y...