Query with multiple joins will only return one of my left joins

I have trouble with a query, the problem is that i am left joining three times from the same table.

The query in question is this:

SELECT
      o.order_id, 
      n.title, 
      c.first_name,
      tdv5.tid,
      tdv6.name,
      tdv8.name AS settlement_month
 FROM orders o
      join products p           on o.product_id = p.nid
      join node n               on p.nid = n.nid
      join customers c          on o.customer_email = c.customer_email
      join term_node tn         on tn.nid = p.nid
      join term_data tdv6       on tn.tid = tdv6.tid AND tdv6.vid = 6
      left join term_data tdv5  on tn.tid = tdv5.tid AND tdv5.vid = 5
      left join term_data tdv8  on tn.tid = tdv8.tid AND tdv8.vid = 8

When i am using this query only the first JOIN using tn.tid will work. For an example if the only join of the last three is this:

join term_data tdv6       on tn.tid = tdv6.tid AND tdv6.vid = 6

It will work fine. The same with

left join term_data tdv5  on tn.tid = tdv5.tid AND tdv5.vid = 5

And

left join term_data tdv8  on tn.tid = tdv8.tid AND tdv8.vid 开发者_运维问答= 8

However when i combine them, they won't work at all. Only tdv6 works when i combine the three queries.

Do you have suggestions on what is wrong? Thanks.

If you try putting a WHERE clause, like this:

SELECT
      o.order_id, 
      n.title, 
      c.first_name,
      tdv5.tid,
      tdv6.name,
      tdv8.name AS settlement_month
 FROM orders o
      join products p           on o.product_id = p.nid
      join node n               on p.nid = n.nid
      join customers c          on o.customer_email = c.customer_email
      join term_node tn         on tn.nid = p.nid
      join term_data tdv6       on tn.tid = tdv6.tid
      left join term_data tdv5  on tn.tid = tdv5.tid
      left join term_data tdv8  on tn.tid = tdv8.tid
WHERE
      tdv6.vid = 6 and
      tdv5.vid = 5 and
      tdv8.vid = 8

Are you sure your conclusions are correct? The null values will appear on the same row, so when there are no records where term_data.tid= = tn.tid and term_data.vid in (5, 8), you will only get the records for tdv6. tid and/or settlement_month will be NULL for those records.

Did you maybe mean to left join tdv6 as well? It is now an inner join, meaning that you need a record in tdv6 to get the records from tdv5 or tdv8 with the same tid.