Multiple use of LEFT JOIN brings only 1 row

join, left-join, mysql, outer-join, sql

Solution

The reason that your `left join` is behaving the same as an `inner join` is because you have additional criteria for your `left join` in your `where` clause. This essentially turns your `outer join` into an `inner join`.

The reason for this is because if `usertagview.tag_id` is `NULL` in the case where there is no matching record, your `IN` statement in your `WHERE` clause eliminates the row with the `NULL` value.

To correct this, you could move your `usertagview.tag_id IN ...` check into your join's `ON` clause.

However, this is only half of your problem. You're only checking the views for the specific tag that was entered by the user, but if I understand your actual requirements, you want to check the views for any tags that are associated with any image that has a tag that matches your search term.

For example, when the user enters `flower`, you want to first find any image that is tagged with `flower`, and then check the views for all other tags for that set of images.

I believe the following query accomplishes this, and this SQL Fiddle shows the query in action:

SELECT
  i.id AS image_id,
  i.title AS image_title,
  IFNULL(SUM(utv.view_count), 0) AS associated_view_totals
FROM
  imagetag originalTag
  JOIN imagetag associatedTags 
    ON associatedTags.image_id = originalTag.image_id
  JOIN image i 
    ON i.id = associatedTags.image_id
  LEFT JOIN usertagview utv 
    ON utv.user_id = 1
    AND utv.tag_id = associatedTags.tag_id
WHERE
  -- User searches for flower tag (Let's assume 5 == flower)...
  originalTag.tag_id IN (5)
GROUP BY
  i.id,
  i.title
ORDER BY 
  associated_view_totals DESC

Problem

It is an intelligent tag base image search system. User adds images with its proper tags in such a table: ``` image (id, title, ...) tag (id, title) /* It doesn't matter who has created the tag*/ imagetag (image_id, tag_id) /* One image may have multiple tags */ ``` User views images and the visits from *those images' tags* are logged in `usertagview` table. (Note that I've used an `INSERT ON DUPLICATE UPDATE` query for that purpose.) ``` usertagview (user_id, tag_id, view_count) ``` Now please consider some images with the following tags: - `river`, `day` (It's a picture that shows a river in a sunny day) - `river`, `night` (That river at the light of the midnight moon) - `tree`, `day` - `tree`, `night` - `flower`, `day` - `flower`, `night` User searches for the tag `river` and any images that has the tag river is displayed: In this case the first image (tagged by river day) and the second (tagged by river night) are shown. User views the second image (tagged by `river` and `night`) and view it is logged in the table `usertagview`. Then the user tries a new search for the tag `tree` and views the `tree night` image. I want that if the user searches for `flower`, the `flower night` be preferred over the `flower day`. I mean `flower night` should come before `flower day`. In other words I want a query that lists images tagged by `flower` according to user's previous views. (`flower night` first, OTHER `flower`s next). My query that was failed: ``` SELECT DISTINCT (image.id) AS image_id, image.title AS image_title, SUM(usertagview.view_count) AS SUM_of_all_tag_views_for_each_image FROM (image) JOIN imagetag ON imagetag.image_id = image.id **LEFT JOIN** usertagview ON usertagview.tag_id = imagetag.tag_id AND usertagview.user_id = {$user_id_from_php} WHERE imagetag.tag_id IN ( {impolde(',', $array_of_id_of_tags_that_the_user_has_entered)} ) AND usertagview.tag_id IN (SELECT tag_id FROM imagetag WHERE userimagetag.image_id = image.id) ORDER BY SUM_of_all_tag_views_for_each_image DESC ``` THE PROBLEM is that the `**LEFT JOIN**` in my query has no difference with a normal `INNER JOIN`. They both have the same result. Even if I use `RIGHT JOIN` it will have no difference.

Original source