CODEWITHSUNDEEP
mysqlsqlselect
KevinDunbar
KevinDunbar

Reputation: 35

SQL Select Query for favorites system

I'm trying to create a favorite system for my website. I have a user table, job table and a favorites table. When a user adds a job to their favorites, their userId and that jobsId is saved to the favorites table. When the user wants to see their favorites I've been trying to select all the rows in the favorites table that have the same userId as the current user. I then need to select the jobId's from these rows and select all the rows in the job table that have a matching jobId.

I've been trying variations of this query but haven't had any luck.

$sqlQuery = "SELECT * FROM job WHERE id = favorites.jobId AND :userId = favorites.userId"

Upvotes: 0

Views: 916

Answers (2)

Thorsten Kettner
Thorsten Kettner

Reputation: 95053

You want all records from the jobs table whose IDs are in the user's favorites. This translates to:

select * from jobs where id in
(
  select jobid from favorites where userid = :userid
);

Upvotes: 1

MikeS
MikeS

Reputation: 1764

How about:

Select j.*
from job j
      join favorites f on j.id = f.jobId
where :userId = f.userId

Upvotes: 1

Related Questions