LEFT JOIN仅返回所需结果的一半

I currently have:

我目前有:

SELECT * FROM submissions AS s
    LEFT JOIN submission_category AS sc ON s.submission_category = sc.sub_cat_id    
    JOIN hospitals      AS h  ON h.hospital_id        = sc.main_hospital_id
    JOIN products       AS p  ON p.product_id         = s.product_id
    JOIN product_group  AS pg ON pg.id                = p.product_group_id 
    JOIN progress_steps AS ps ON sc.approval_step     = ps.step_id
    WHERE s.approval_status='2' 
    AND sc.user_id='$mask5'

The problem is that sc has 3 rows and s has 6 rows. I need to return all six rows but currently it is only returning 3, one of each from s that corresponds to sc.

问题是sc有3行,s有6行。我需要返回所有六行,但是目前它只返回3,其中一个来自对应于sc的s。

Based on the suggestion I am now trying:

根据我现在尝试的建议:

SELECT * FROM submissions AS s
LEFT JOIN submission_category AS sc ON (s.submission_category = sc.sub_cat_id AND sc.user_id='$mask5' ) 
JOIN hospitals      AS h  ON h.hospital_id        = sc.main_hospital_id
JOIN products       AS p  ON p.product_id         = s.product_id
JOIN product_group  AS pg ON pg.id                = p.product_group_id 
JOIN progress_steps AS ps ON sc.approval_step     = ps.step_id
WHERE s.approval_status='2'

But still getting three rows, not six?

但仍然得到三排,而不是六排?

1 个解决方案

#1

Having columns of LEFT JOINed table[s] in WHERE "turns" LEFT JOIN into INNER (sc.user_id = '$mask5' always returns NULL or false in case there is no corresponding row in submission_category ). Move sc.user_id=... into join condition :

在WHERE中将LEFT JOINed表[s]的列“转”为LEFT JOIN到INNER(sc_user_id ='$ mask5',如果submission_category中没有相应的行,则返回NULL或false)。将sc.user_id = ...移动到连接条件:

....
LEFT JOIN submission_category AS sc ON (s.submission_category = sc.sub_cat_id AND 
 sc.user_id='$mask5' )
...