CODEWITHSUNDEEP
phpmysqlsql
Ben
Ben

Reputation: 8991

SQL query construction for finding rows from multiple values in another table

I have two tables: jobs and applications.

jobs has columns including id (the key) and user_id (the ID of the user who created the row).

applications has columns including job_id (the ID of the job which the application correlates to).

The query I'm trying to construct will perform the following:

  1. Search for all rows in the jobs table that has a specific user_id
  2. Search for all rows in the applications table whose job_id correlates to the ids returned in the previous search

e.g.

jobs table

id    user_id    ...>
1     1          ...
2     3          ...
3     1          ...

applications table

id    job_id     ...>
1     3          ...
2     1          ...
3     2          ...

If I run the query where user_id=1, it should return rows 1 and 2 from the applications table as they correlate to the jobs where user_id=1.

How can this be accomplished?

Upvotes: 0

Views: 111

Answers (2)

Nightmaresux
Nightmaresux

Reputation: 538

You can just join these two tables to make one "bigger table" with all of your data. You will join them on common column, in your case its Id = job_id.

The query will look like this :

select a.* 
from applications a 
join jobs j on a.job_id = j.id  
where j.user_id = '1'

Upvotes: 0

President Camacho
President Camacho

Reputation: 1910

  select app.* from applications app, jobs j
  where j.id = app.job_id
  and j.user_id = '1'

Using join.

Upvotes: 2

Related Questions