PHP/MySQL - Select based on another result

Question

Hey people, looking for some help here.

Basically I have two tables in a MySQL database, jobs and dates.

Each date references a job by id, sometimes more than one date reference a single job.

I'm doing this query (x being a variable integer):

SELECT * FROM jobs LIMIT x,50

Now I have a result with 50 job-records and I would like to retrieve all dates pointing to these 50 jobs in one query.

How can this be done without iterating over the result and making a query for each iteration?

Hope I was clear enough :S

PS: Didn't manage to draw a decent table with a little mysql structure.

A+ for not going the iteration route, I've spent this week painfully fixing several needlessly deep query iterations from past developers. — Chad
– Chad, Commented Nov 5, 2010 at 18:16

Nikola K. · Accepted Answer · 2013-01-18 00:09:30Z

1

SELECT *
FROM jobs
INNER JOIN dates
    ON dates.job_id = jobs.id
LIMIT x,50

This would limit it to ONLY those jobs that DO HAVE dates associated with them. So if you want to see jobs that might might not have dates associated, use an OUTER join:

SELECT *
FROM jobs
LEFT JOIN dates ON dates.job_id = jobs.id
LIMIT x, 50

edited Jan 18, 2013 at 0:09

Nikola K.

7,15313 gold badges33 silver badges39 bronze badges

answered Nov 5, 2010 at 17:47

Viper_Sb

1,81714 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Nikola K. · Accepted Answer · 2013-01-18 00:08:31Z

1

There are two ways you can do this.

Use the IN keyword.

SELECT * 
FROM dates 
WHERE job_id 
    IN (1, 2, 3, 4, ..)

Select it all at once.

SELECT * 
FROM jobs 
LEFT JOIN dates 
    ON jobs.job_id = dates.job_id 
LIMIT x, 50

That will give you a row for each (job, date) combination, with potentially multiple jobs for each date.

edited Jan 18, 2013 at 0:08

Nikola K.

7,15313 gold badges33 silver badges39 bronze badges

answered Nov 5, 2010 at 17:46

Chris Moos

6916 silver badges7 bronze badges

Collectives™ on Stack Overflow

PHP/MySQL - Select based on another result

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related