Select Date query issue

[Nollyvenon]

I have a query which i am trying to get the last date and when i do, the purchdate and LastPayDate is the same for all the data. the query is below:

SELECT DISTINCT customer.custid,customer.compname,customer.balance AS balance,customer.phone,acctofficer.acctfullname AS acctoff, preAgg1.LastPayDate,customer.depot, MAX(mastorders.lastsuppdate) AS purchdate, MAX(mastorders.id) FROM customer,mastorders,acctofficer, (SELECT DISTINCT T.custid, Max(T.timestamp) AS LastPayDate FROM transactions AS T,customer AS C WHERE T.parttype='Receipt' AND T.custid=C.custid GROUP by T.custid) preAgg1 WHERE (STR_TO_DATE(mastorders.lastpaydate,'%d-%m-%Y') < DATE_SUB(CURDATE(), INTERVAL 30 DAY)) AND (STR_TO_DATE(mastorders.lastpaydate,'%d-%m-%Y')<> '0000-00-00') AND customer.balance>0 AND customer.custid=mastorders.custid AND acctofficer.acctoffid=mastorders.acctoff AND acctofficer.acctoffid=customer.acctoff AND customer.depot='$depot' GROUP by customer.custid,STR_TO_DATE(mastorders.lastpaydate, '%d-%m-%Y') ASC

Thanks for the help.

[Fernando_4]

Wow, you gotta work on indentation... it's a pain to read through this. I'm going to repost the query with indentation and code high-lighting for the next guy. At this point I'm still not sure I can help though...

QUERY:

SELECT DISTINCT 
    customer.custid, customer.compname, customer.balance 
AS balance, customer.phone, acctofficer.acctfullname 
AS acctoff, preAgg1.LastPayDate,customer.depot, MAX(mastorders.lastsuppdate) 
AS purchdate, MAX(mastorders.id) 
FROM customer, mastorders, acctofficer, (
    SELECT DISTINCT 
        T.custid, 
    MAX(T.timestamp) AS LastPayDate 
    FROM transactions AS T, customer AS C 
    WHERE T.parttype='Receipt' 
    AND T.custid=C.custid 
    GROUP BY T.custid) 
preAgg1 
WHERE (STR_TO_DATE(mastorders.lastpaydate,'%d-%m-%Y') < DATE_SUB(CURDATE(), INTERVAL 30 DAY)) 
AND (STR_TO_DATE(mastorders.lastpaydate,'%d-%m-%Y')<> '0000-00-00') 
AND customer.balance > 0 
AND customer.custid = mastorders.custid 
AND acctofficer.acctoffid = mastorders.acctoff 
AND acctofficer.acctoffid = customer.acctoff 
AND customer.depot = '$depot'  
GROUP BY customer.custid, STR_TO_DATE(mastorders.lastpaydate, '%d-%m-%Y') ASC

After breaking this up, into what I think is the proper indentation, I get the feeling it's too advanced for me. I'll try to look into it deeper, but I have a feeling I'm out of my depth on this one.

EDIT

Do you have any relationship diagram to help us see what you have there? Or the table's create query so we can visualize the tables that go along with this query and try to replicate. I'm having a hard time seeing what you need.

Maybe you can get an SQLFiddle going.

[diafol]

Seems this thread psoted at the same time. May be some crossover here:

http://www.daniweb.com/web-development/php/threads/475914/issue-on-join-query-in-php#post2078497

[Fernando_4]

@diafol, seems it's the same database, but different query intentions, but I've never used column AS column before. Tried it on my db, but only got errors back. And google isn't my friend today (i'm not asking the right questions most likely).

Is that possible? And what is that called? Gotta learn that, seems it might come in handy someday.

Also the SELECT * FROM a, (SELECT * FROM b) I've never used that either. Seems like some sort of chaining, not sure if it could be replaced by some sort of join though.

Any pointers/direct answers/or links to read up on would be appreciated.

[diafol]

SELECT * FROM a, (SELECT * FROM b)... is called subquerying and you can sometimes/usually replace with a JOIN.

oldcolumnname AS newcolumnname is known as aliasing and it's very handy in a number of situations:

1) for reducing the size/perceived complexity of queries when dealing with multiple tables, e.g.

SELECT c.country, s.state FROM countries as c INNER JOIN states as s ON ...

instead of

SELECT countries.country, states.state FROM countries INNER JOIN states...

2) "disambiguation" of same name fields:

SELECT countries.id AS cid, states.id AS sid FROM countries INNER JOIN states ...

otherwise 'id' in $row['id'] would be ambiguous. Now easy with $row['cid'] or $row['sid']

You also need them for referencing subqueries too or sometimes multiple relationships to the same field.

[Fernando_4]

Ok, then I knew about table aliasing, but not field aliasing. So I'm guessing the indentation I provided was actually quite off. I'll give that an edit.

About the subquerying... I did some digging around and saw someone mentioning the term derived tables seems to be the same thing, although that link seems to discourage it.

Thanks for the info diafol^^

EDIT
Seems I can only edit for a certain period. Can't fix my booboo :(