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:
SELECT
jobcost_projection.jobid,
jobcost_projection.phaseid,
Date(jobcost_projection.submitted) As submitted,
jobcost_projection.projection
FROM
jobcost_projection
WHERE
jobcost_projection.jobid = 940848 

Open in new window


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.Phaseid

Open in new window


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.
servicecoAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
you can try customize this query:

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
timecards.Jobid,
timecards.Phaseid

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Surranoapplication managerCommented:
Ryan's right but make sure to use the alias "a" instead of table name proper in the group by clause as well.
servicecoAuthor Commented:
Thank you! That was definitely the help I needed.  I did not understand I could pass the parameter in the ON statement.
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
tks for reminder by Surrano.

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 

Open in new window


@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
Surranoapplication managerCommented:
Personally I prefer the form without the JOIN keyword:
Select... From a, b where a.x=b.y...
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.