Mysql query not return correctly

Question

I am having problem with these result. I have been trying but no luck.

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

This appears to be a simple join between all 3 tables. what have you tried?; — xQbert
– xQbert, Commented Apr 20, 2017 at 20:26
@xQbert thanks for your response. I have tried something similar to JNevill suggestion but in different order of query. I will try following his suggestion to see — daniel8x
– daniel8x, Commented Apr 20, 2017 at 20:53

JNevill · Accepted Answer · 2017-04-20 20:31:55Z

2

So... it seems like you want something like:

SELECT
    jobs.id,
    jobs.title,
    jobs....,
    CASE WHEN following_job.id_user = 3 THEN 1 ELSE 0 END as following_job
FROM
    jobs
    INNER JOIN job_user ON job.id = job_user.id_job
    LEFT OUTER JOIN JOIN following_job on job.id = following_job.id_job
WHERE
    job_user.id_user <> 3;

Joining all three tables according to your schema. Filtering via the WHERE clause to insure that no jobs that are no jobs that user 3 has. And then a CASE (or IF()) statement to flag the job as followed by user 3.

edited Apr 20, 2017 at 20:31

answered Apr 20, 2017 at 20:29

JNevill

50.6k4 gold badges46 silver badges72 bronze badges

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

5 Comments

xQbert Over a year ago

Inner join on following_job will eliminate all but ID_USER 3 and then the where clause eliminates it right so... No records returned?

JNevill Over a year ago

I think in Left Joins and then write INNER JOINs because I'm special. I have fixed it. :/

xQbert Over a year ago

I like special people they make me smile :P

daniel8x Over a year ago

@JNevill Thank you. let me try. to see if that work. I have been thinking it in a wrong way of combining those 3 tables

daniel8x Over a year ago

@JNevill I got it work. Thanks a lot. Marked as the answer.

Collectives™ on Stack Overflow

Mysql query not return correctly

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related