I have two three tables. users, jobs and users_jobs. One user can have many jobs and one job can have many users.
Here is my users table:
+----+------+--------+
| ID | Name | gender |
+----+------+--------+
| 1 | Bob | male |
| 2 | Sara | female |
+----+------+--------+
my jobs table:
+----+----------+
| id | job_id |
+----+----------+
| 1 | Engineer |
| 2 | Plumber |
| 3 | Doctor |
+----+----------+
users_jobs table:
+---------+--------+
| user_id | job_id |
+---------+--------+
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 2 | 1 |
+---------+--------+
As an example, i want to select all males and check if they have at least 1 job and then return their info. If they have no jobs, then don't select/return that user. This is my query:
SELECT * FROM users
INNER JOIN users_jobs
ON users_jobs.user_id = users.id
WHERE users.gender = 'male'
But it returns Bob's info 3 times since he has 3 jobs. I don't want duplicates, how can I get rid of duplicates without using DISTINCT or GROUP BY since performance is very important here.
Thank you!
To follow on from the comments, for performance, it's necessary to use a distinct in your query, try:
SELECT DISTINCT Name FROM users
INNER JOIN users_jobs
ON users_jobs.user_id = users.id
WHERE users.gender = 'male'
If you're looking to get all the columns but keep the id's distinct you can use a GROUP BY, try:
SELECT * FROM users
INNER JOIN users_jobs
ON users_jobs.user_id = users.id
WHERE users.gender = 'male'
GROUP BY users.id
Although this will also effect performance, it depends on what you prioritize the most.