how to retrieve data with multiple join from multiple tables with status columns

Question

I have three tables. Boys_dress, Girls_dress and sleeve_dress

Boys_dress

id dress_name  status
1    sample1    1
2    Sampel2    1
3    Sampel3    1

Girls_dress

id    dress_name  status
1    Sampel4       1
2    Sampee5       1
3    Sampel6       1

Sleeve_dress

id boys_dress_id  girls_dress_id sleeve_name
1    2                2             Full
1    1                1             Half
1    1                3             Full
1    2                null          Full

From the above boys_dress_id and girls_dress_id are optional columns. any one among boys_dress_id and girls_dress_id column must have data. retrieving data from sleeve_dress table with dress name by joining the dress tables having data with status = 1

I retrieve data with the below MySQL query. But I can't get the data if the boys_dress_id or girls_dress_id is null because of condition status = 1. I need to retrieve data for boys_dress_id even if the girls_dress_id is null.

select * 
from sleeve_dress sd 
    left join Boys_dress bd on bd.id = sd.boys_dress_id 
    left join girls_dress gd on gd.id = sd.girls_dress_id 
where bd.status = 1 
  AND gd.status = 1

Answer 1

Move the conditions on status to the on clauses:

select . . .  -- explicitly put the column names here
from sleeve_dress sd left join
     Boys_dress bd
     on bd.id = sd.boys_dress_id and bd.status = 1 left join
     girls_dress gd
     on gd.id = sd.girls_dress_id and gd.status = 1;

The where clause (as you have figured out) is filtering out the non-matching rows, because a NULL status does not match 1. The on clause makes the condition part of the join condition.

Note that I replaced the select * with select . . .. Reasonable people disagree with whether select * is a good idea with one table. However, you have multiple tables with overlapping column names. In that case, you want to explicit list the columns you want, probably giving them particular aliases (such as gd.status as gd_status).