Multiple inner joins with different where-conditions

Question

I am currently using the following query to perform a search across multiple tables via INNER JOIN.

    SELECT title, m2.txt1 AS teaser, inputdat, db_file.*
       FROM db_item 
            INNER JOIN db_itemv AS m1 USING(id_item) 
            INNER JOIN db_itemf USING(id_item) 
            INNER JOIN db_itemd USING(id_item) 
            INNER JOIN db_itemv AS m2 USING(id_item)
       WHERE type=15 AND m1.fldnr='12' 
AND m1.indik='b' AND m1.txt1s='en'
 AND visibility=0 AND inputdat>'2005-11-02' AND m2.fldnr='123'
       GROUP BY title
       ORDER BY inputdat DESC
       LIMIT 0,100

The columns of db_itemv (m1, m2) are named:

ID   id_item   fldnr   indik   txt1

One id_item may exist on several rows.

My problem is, that m2.fldnr='123' does not always exist and therefore it kicks out the whole row. But I want to keep the row and keep the column empty when it doesn't exist in the row. How can I solve the problem?

Solution 1

I think there are a couple of issues here, so bear with me whilst I go through them :)

I agree with @Jeurgen that you need to use LEFT JOIN instead of INNER JOIN, which will enable you to retrieve all the rows that you are searching for, including those without an entry in m2.

However, as your where clause specifically states only give me that data which DOES HAVE an m2.fldnr value equal to '123', then it is correct for you not to have any rows which do not having matching m2 rows, as they cannot meet your criteria.

I think what you are trying to do is pull all data from the first four tables, and then only data within the m2 table that contains '123', if this is the case, then you need to relocate your m2.fldnr filter, and handle those missing rows...

My version of your query is thus as follows - this has not been tested, as I don't have sample data with your structure, but give it a try, and vary it if needed;

SELECT title, m2.txt1 AS teaser, inputdat, db_file.*
FROM db_item 
    INNER JOIN db_itemv AS m1 USING(id_item) 
    INNER JOIN db_itemf USING(id_item) 
    INNER JOIN db_itemd USING(id_item) 
    LEFT JOIN  db_itemv AS m2 
      ON db_item.id_item = m2.id_item
      AND ( m2.fldnr = '123' OR m2.fldnr IS NULL )
WHERE type=15 
    AND m1.fldnr = '12' 
    AND m1.indik = 'b'
    AND m1.txt1s = 'en'
    AND visibility = 0 
    AND inputdat > '2005-11-02' 
GROUP BY title
ORDER BY inputdat DESC

Hope that helps :)

Solution 2

Replace all inner join with left outer join