MySQL Query not displaying correctly

Question

I am having to set up a query that retrieves the last comment made on a customer, if no one has commented on them for more than 4 weeks. I can make it work using the query below, but for some reason the comment column won't display the latest record. Instead it displays the oldest, however the date shows the newest. It may just be because I'm a noob at SQL, but what exactly am I doing wrong here?

SELECT DISTINCT
    customerid, id, customername, user, MAX(date) AS 'maxdate', comment
FROM comments
WHERE customerid IN 
   (SELECT DISTINCT id FROM customers WHERE pastdue='1' AND hubarea='1') 
AND customerid NOT IN 
   (SELECT DISTINCT customerid FROM comments WHERE DATEDIFF(NOW(), date) <= 27)
GROUP BY customerid
ORDER BY maxdate

The first "WHERE" clause is just ensuring that it shows only customers from a specific area, and that they are "past due enabled". The second makes sure that the customer has not been commented on within the last 27 days. It's grouped by customerid, because that is the number that is associated with each individual customer. When I get the results, everything is right except for the comment column...any ideas?

Geek Num 88 · Accepted Answer · 2012-10-06 05:54:55Z

1

Join much better to nested query so you use the join instead of nested query Join increase your speed this query resolve your problem.

SELECT DISTINCT customerid,id, customername, user, MAX(date) AS 'maxdate', comment FROM comments inner join customers on comments.customerid = customers.id WHERE comments.pastdue='1' AND comments.hubarea='1' AND DATEDIFF(NOW(), comments.date) <= 27 GROUP BY customerid ORDER BY maxdate

edited Oct 6, 2012 at 5:54

Geek Num 88

5,3102 gold badges24 silver badges37 bronze badges

answered Oct 6, 2012 at 5:47

Rajendra

3321 silver badge5 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jagmag · Accepted Answer · 2012-10-06 05:40:56Z

0

I think this might probably do what you are trying to achieve. If you can execute it and maybe report back if it does or not, i can probably tweak it if needed. Logically, it ' should' work - IF i have understood ur problem correctly :)

SELECT X.customerid, X.maxdate,  co.id, c.customername, co.user, co.comment
FROM 
    (SELECT customerid, MAX(date) AS 'maxdate'
    FROM comments cm
    INNER JOIN customers cu ON cu.id = cm.customerid
    WHERE cu.pastdue='1' 
    AND cu.hubarea='1' 
    AND DATEDIFF(NOW(), cm.date) <= 27)
    GROUP BY customerid) X
INNER JOIN comments co ON X.customerid = co.customerid and X.maxdate = co.date
INNER JOIN customer c  ON X.customerid = c.id
ORDER BY X.maxdate

answered Oct 6, 2012 at 5:40

Jagmag

10.4k1 gold badge37 silver badges60 bronze badges

1 Comment

Jason Raines Over a year ago

I had to tweak it just a bit, but this was it! Thank you so much for all of your help.

John Woo · Accepted Answer · 2012-10-06 05:39:47Z

You need to have subquery for each case.

SELECT  a.*
FROM    comments a
        INNER JOIN
        (
            SELECT customerID, max(`date`) maxDate
            FROM    comments
            GROUP BY customerID
        ) b ON a.customerID = b.customerID AND
                a.`date` = b.maxDate
        INNER JOIN
        (
            SELECT DISTINCT ID
            FROM customers
            WHERE pastdue = 1 AND hubarea = 1
        ) c ON c.ID = a.customerID
        LEFT JOIN
        (
            SELECT DISTINCT customerid 
            FROM comments 
            WHERE DATEDIFF(NOW(), date) <= 27
        ) d ON a.customerID = d.customerID
WHERE   d.customerID IS NULL

The first join gets the latest record for each customer.
The second join shows only customers from a specific area, and that they are "past due enabled".
The third join, which uses LEFT JOIN, select all customers that has not been commented on within the last 27 days. In this case,only records without on the list are selected because of the condition d.customerID IS NULL.

But tomake your query shorter, if the customers table has already unique records for customer, then you don't need to have subquery on it.Directly join the table and put the condition on the WHERE clause.

SELECT  a.*
FROM    comments a
        INNER JOIN
        (
            SELECT customerID, max(`date`) maxDate
            FROM    comments
            GROUP BY customerID
        ) b ON a.customerID = b.customerID AND
                a.`date` = b.maxDate
        INNER JOIN customers c
            ON c.ID = a.customerID
        LEFT JOIN
        (
            SELECT DISTINCT customerid 
            FROM comments 
            WHERE DATEDIFF(NOW(), date) <= 27
        ) d ON a.customerID = d.customerID
WHERE   d.customerID IS NULL AND
        c.pastdue = 1 AND 
        c.hubarea = 1

Ali Motamedi · Accepted Answer · 2012-10-06 05:45:11Z

0

Two of your table columns are not contained in either an aggregate function or the GROUP BY clause. for example suppose that you have two data rows with the same customer id and same date, but with different comment data. how SQL should aggregate these two rows? :( it will generate an error...

answered Oct 6, 2012 at 5:45

Ali Motamedi

871 silver badge2 bronze badges

Comments

Daniel Rodriguez Mendez · Accepted Answer · 2012-10-06 05:47:34Z

0

try this

select  customerid, id, customername, user,date, comment from(
  select customerid, id, customername, user,date, comment, 
      @rank :=  IF(@current_customer = id, @rank+ 1, 1),
      @current_customer := id
    from comments
    where customerid IN 
      (SELECT DISTINCT id FROM customers WHERE pastdue='1' AND hubarea='1') 
      AND customerid NOT IN 
      (SELECT DISTINCT customerid FROM comments WHERE DATEDIFF(NOW(), date) <= 27)
    order by customerid, maxdate desc
) where rank <= 1

edited Oct 6, 2012 at 5:47

answered Oct 6, 2012 at 5:35

Daniel Rodriguez Mendez

1671 silver badge7 bronze badges

Collectives™ on Stack Overflow

MySQL Query not displaying correctly

5 Answers 5

Comments

1 Comment

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related