CODEWITHSUNDEEP
mysqlsql
Tristan Ross
Tristan Ross

Reputation: 117

Conditional Left Join or Where SQL query

I have queries that displays some data then i left join one table but there's a time, that table has no data.

Is there any possible query to add conditional left join or where clause?

$sql = "SELECT 
                mm.idno as idno,
                TRIM(UPPER(CONCAT(mm.fname, ' ', mm.mname, ' ', mm.lname))) as fullname, 
                TRIM(UPPER(mm.branchname)) as branchname, 
                TRIM(UPPER(mm.address)) as outlet_address, 
                (SUM(dr.totalamt)/GREATEST(1,COUNT(DISTINCT drr.id)) - SUM(drr.totalamt)/GREATEST(1,COUNT(DISTINCT dr.id))) as total_amount
                FROM 8_membermain mm
                LEFT JOIN 8_directsalessummary dr
                ON mm.idno = dr.idno
                LEFT JOIN 8_drreturnsummary drr
                ON mm.idno = drr.idno
                WHERE mm.status > 0 AND dr.status = 1 
                AND dr.ispaid = 'Full Payment'
                AND dr.trandate BETWEEN '".$date1."' AND '".$date2."'
                AND drr.trandate BETWEEN '".$date1."' AND '".$date2."'";

sometimes there's no data to get in 8_drreturnsummary but i added AND drr.trandate BETWEEN '".$date1."' AND '".$date2."'" so there's a wrong in my query. I just want 8_drreturnsummary to be optional/conditional as well as WHERE ... AND drr.trandate. I tried any possible way but i failed.

Upvotes: 0

Views: 86

Answers (1)

jefftrotman
jefftrotman

Reputation: 1114

I'm going to use a simpler query as my example. In this query:

select *
from TableA left outer join TableB on TableA.Field1 = TableB.Field1
where TableB.Field2 = 'somevalue'

the WHERE clause excludes any unmatched TableA records because all TableB values would be NULL in that case. You can think of this as a "post-join filter".

I think you want a "pre-join filter". You can do that this way:

select *
from TableA left outer join 
(select * from TableB where TableB.Field2 = 'somevalue') as TableB 
on TableA.Field1 = TableB.Field1

Upvotes: 1

Related Questions