SELECT `project`.* FROM `project` LEFT JOIN `project_to_app` ON `project`.`project_id` = `project_to_app`.`project_id` WHERE (`project`.`created_by`=2) AND (`project_to_app`.`app_id`<>'userapp56g')

I have a problem understanding the query construction. There are 4 tables: project , project_to_app , app , user . Only the first two participate in the request. project_to_app is a link table containing the fields id , project_id , app_id .

My task is to list the projects created by the user with id = 2 ( project . created_by = 2), where created_by is a link to the user table in the project table, and not belonging to the current application ( project_to_app . app_id <> 'userapp56g'), where ' userapp56g 'is the id of the current application. The request returns an empty response to me, although there are two projects, both belong to the current user, but there is only one connection with them. Help to understand where the flaw in the logic?

  • And if for one project there are 2 records of different applications in to_app you are satisfied that this project will be displayed twice (now you have exactly this)? And when there is no connection at all, app_id is null and any comparison will be false - Mike

1 answer 1

A flaw in the logic in this place that the condition project_to_app . app_id you impose in where - i.e. you say that you need to return only those rows where the value of this field does not match the value. Here, even the meaning of the left join is lost, since null fields by definition return false for any comparison with a fixed value.

And in fact, you need to attach only those lines where the value of this field does not match the value - so transfer the condition to on when joining. Like this:

 SELECT `project`.* FROM `project` LEFT JOIN `project_to_app` ON `project`.`project_id` = `project_to_app`.`project_id` AND (`project_to_app`.`app_id`<>'userapp56g') WHERE (`project`.`created_by`=2)