MySQL query is not returning correctly

Question

--Edit

I still dont get it for some reason. I will give more detail about my problem.

jobs table

   id, title ... all details
    1, title1,...
    2, title2,...
    3, title3,...
    4, title4,...

job_user table

id,id_job,id_user
1,1,1
2,2,3
3,3,3
4,4,4

following_job table

id,id_job,id_user
1, 1, 3

So basically, user 3 has 2 jobs (2,3), and he follows job 1 of user 1. so, if i login as user 3, i would like to get all details jobs of user <> 3 (just the requirement that i need to do). i would get the result

id,id_job,id_user
    1,1,1 
    4,4,4

My goal results would be :

id,title..., following_id
        1,title1,...,1
        4,title4,...,0

the following_id will be added as result above, since user 3 followed id_job 1 so its following_id = 1 else = 0. And id_job 1,4 will joined with jobs table to get details about it : title ...

I am doing the follow/unfollow job functionality

Thanks all

Do you have a table which contains the job IDs for which you want to search? — Tim Biegeleisen
– Tim Biegeleisen, Commented Apr 20, 2017 at 6:10
Why is it id 2 for id_job 3 in your results? The table associates id 1 with it. — Thorsten Kettner
– Thorsten Kettner, Commented Apr 20, 2017 at 6:14
Your id column in your result is nonsensical. That aside, consider handling the logic of missing values in application code (e.g. PHP), if that's available. — Strawberry
– Strawberry, Commented Apr 20, 2017 at 6:14
Thank you guys for quick answer. i edited my question including my full query — daniel8x
– daniel8x, Commented Apr 20, 2017 at 6:17

Tim Biegeleisen · Accepted Answer · 2017-04-20 06:14:39Z

3

Typically you would have a second table which contains the id_job values you want to search/match, something like this:

has_jobs

id_job | (other columns...)
2
3

Then, you could just left join has_jobs to following_job to get the result you want:

SELECT t2.id
       t1.id_job,
       CASE WHEN t2.id_job IS NULL THEN 0 ELSE 1 END AS following_id
FROM has_jobs t1
LEFT JOIN following_job t2
    ON t1.id_job = t2.id_job

If you don't have an actual has_jobs table, then you could use an inline table instead:

SELECT t2.id
       t1.id_job,
       CASE WHEN t2.id_job IS NULL THEN 0 ELSE 1 END AS following_id
FROM
(
    SELECT 2 AS id_job
    UNION ALL
    SELECT 3
    -- add more IDs here if wanted
) t1
LEFT JOIN following_job t2
    ON t1.id_job = t2.id_job

answered Apr 20, 2017 at 6:14

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

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

1 Comment

daniel8x Over a year ago

Biegeleisn : Thanks for your answer. I have been trying but no luck. Can you please take a look my post that i edit. Thanks much

Collectives™ on Stack Overflow

MySQL query is not returning correctly

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related