Issue on Join query in php

[Nollyvenon]

Please can i get a help on joining these 3 queries

SELECT customer.id,customer.custid,customer.compname, customer.balance AS balance,customer.phone,acctofficer.acctfullname AS acctoff, STR_TO_DATE(mastorders.lastpaydate,'%d-%m-%Y') AS debtage,customer.depot, STR_TO_DATE(mastorders.lastsuppdate,'%d-%m-%Y') AS purchdate FROM customer,mastorders,acctofficer WHERE customer.custid=mastorders.custid and acctofficer.acctoffid=customer.acctoff GROUP by customer.id ASC

SELECT customer.id,customer.custid,customer.compname, customer.balance AS balance,customer.phone,acctofficer.acctfullname AS acctoff, customer.depot FROM customer,acctofficer WHERE acctofficer.acctoffid=customer.acctoff GROUP by customer.id ASC

SELECT DISTINCT C.custid,T.timestamp AS LastPayDate FROM transactions AS T,customer AS C WHERE T.parttype='Receipt' AND T.custid=C.custid GROUP by T.custid

[diafol]

It may help if you stated all the fields that you need to display. There seems to be a lot of duplication, so a list would be useful. I'd imagine something like:

SELECT 
    c.id,
    c.custid,
    c.compname,
    c.balance,
    c.phone, 
    a.acctfullname AS acctoff, 
    STR_TO_DATE(m.lastpaydate,'%d-%m-%Y') AS debtage, 
    c.depot, 
    STR_TO_DATE(m.lastsuppdate,'%d-%m-%Y') AS purchdate, 
    t.timestamp AS LastPayDate 
FROM customer AS c 
    INNER JOIN mastorders AS m 
        ON c.custid=m.custid 
    INNER JOIN acctofficer AS a 
        ON a.acctoffid=c.acctoff 
    INNER JOIN transactions AS t 
        ON t.custid=c.custid
    WHERE t.parttype='Receipt'   
    GROUP BY c.id, c.custid

But unable to test as you don't provide an SQL dump to import.

[Nollyvenon]

Below are the needed fields: compname, balance, phone, acctfullname, LastPayDate, lastsuppdate, depot.

[diafol]

OK:

SELECT 
    c.compname,
    c.balance,
    c.phone, 
    a.acctfullname AS acctoff, 
    STR_TO_DATE(m.lastpaydate,'%d-%m-%Y') AS debtage, 
    c.depot, 
    STR_TO_DATE(m.lastsuppdate,'%d-%m-%Y') AS purchdate, 
    t.timestamp AS LastPayDate 
FROM customer AS c 
    INNER JOIN mastorders AS m 
        ON c.custid=m.custid 
    INNER JOIN acctofficer AS a 
        ON a.acctoffid=c.acctoff 
    INNER JOIN transactions AS t 
        ON t.custid=c.custid
    WHERE t.parttype='Receipt'   
    GROUP BY c.id, c.custid

Maybe like that?

[Nollyvenon]

Thanks so much. worked perfectly.