SQL: How do I write this tricky piece?

[Lt. Manlove]

Hello all, little tricky one to test the men from the boys! It's been picking my brain for the last week but no joy!

What I want to do is search a `comments` table WHERE the last `comment` for each unique `customer_id` is `LIKE %ERROR%` and WHERE there is no more than 2 `LIKE %APPROVED%`.

Alternatively, any PHP guidance on how to accomplish this.

Remember, result must not have more than two %APPROVED% for same customer_id.

I really hope you can understand what i'm trying to do.

Many thanks

 

[RockLobster]

You could do this using nested sub queries, are they supported in MySQL? Top and Count are the two functions which popped in my brain.

 

[Sic]

SELECT *
FROM comments AS c
WHERE (
 SELECT COUNT(customer_id) AS error_count
 FROM comments
 WHERE comment LIKE '%ERROR%'
 ORDER BY comment_id DESC
 LIMIT 0,1
) = 1 AND (
 SELECT COUNT(customer_id) AS customer_approved
 FROM comments as c3
 WHERE comment LIKE '%APPROVED%' AND c.customer_id = c3.customer_id
) < 3

something like that?

 

[JIMA]

Hi,

Another (untested) way to do this.

SELECT *
FROM comments c1
WHERE c1.comment like %ERROR%
AND  c1.commentid in (SELECT max(commentid) 
                                FROM comments c2
                                WHERE c2.customerid = c1.customerid)
AND c1.customerid in (SELECT c3.customerid
                                FROM comments c3
                               WHERE c3.comment like %APPROVED%
                               GROUP BY customerid
                               HAVING COUNT(*) <2)

The first subselect ensures that the comment that matches error is the last one. The second subselect checks that the customer has no more than two approved comments. Performance of that group by (it not the whole piece of SQL) could be horrible though.

Haven't tested it but Sic's solution and this will give you a few ideas.

Jim

 

[Lt. Manlove]

Will give those two a shot now! The winner gets uber kudos!

 

[Lt. Manlove]

+-------------+-----------+------+-----+-------------------+----------------+
| Field       | Type      | Null | Key | Default           | Extra          |
+-------------+-----------+------+-----+-------------------+----------------+
| id          | int(10)   | NO   | PRI | NULL              | auto_increment |
| comment     | text      | NO   |     |                   |                |
| created     | timestamp | NO   |     | CURRENT_TIMESTAMP |                |
| customer_id | int(10)   | NO   |     | 0                 |                |
| user_id     | int(10)   | NO   |     | 0                 |                |
+-------------+-----------+------+-----+-------------------+----------------+
5 rows in set (0.00 sec)

Example comments:
ERROR: #15005: Processor Decline [This transaction cannot be processed.]
PayPal: Approved £34.99

Can't seem to get it working with Sic's suggestion, still tinkering though. Will try JIMAs approach next. Just posted comments table incase that explains my schema a bit better.

		$data = $this->Customer->query("SELECT *
		FROM comments AS c
		WHERE (
		 SELECT COUNT(customer_id) AS error_count
		 FROM comments
		 WHERE comment LIKE '%ERROR%'
		 ORDER BY id DESC
		 LIMIT 0,1
		) = 1 AND (
		 SELECT COUNT(customer_id) AS customer_approved
		 FROM comments as c3
		 WHERE comment LIKE '%Approved%' AND c.customer_id = c3.customer_id
		) < 3");

		pr($data);
		exit();

Empty array!

Thank you for your help!

 

[Sic]

select *
from comments as c1
where (
select count(*)
from comments as c2
where c2.customer_id = c1.customer_id
and c2.comment like 'ERROR%'
order by id desc
limit 0,1
) = 1 
AND (
select count(*)
from comments as c3
where c3.customer_id = c1.customer_id
and c3.comment like '%approved%'
) < 2
order by id desc

I think that's slightly different to the other one, but I was working directly from your schema this time, so it should work better. are you getting any errors from it or is it just not returning rows? if you want to dump the sql for that table and post it (unless there's sensitive data there), I'll knock something out for you that'll work.

 

[Lt. Manlove]

Cheers for that! How long would you expect that to run for?

Only ask because it never seems to end, aswell as queries provided by the helpful people on IRC.

 

[Sic]

have you indexed all the columns? are you using phpmyadmin?

 

[Lt. Manlove]

I think it is just the id column indexed and i'm using mysql cli.

 

[Sic]

I don't know how to use that one, but you should be able to index your other columns too. probably a good idea to index comment and customer_id. should make the query run faster

 

[JIMA]

Hi,

I'm presuming in the below that MySQL lets you build Views on a base table....

You could use Views to simplify things a bit. For example, construct two views on your comments table, one holding the customerids where the last comment is like %ERROR% and the second where the customer has two %APPROVED% entries. Then you just need to compare the information exposed through the Views which will make your final SQL much simpler. The views can be written in such a way that they will maintain themselves as rows are added, removed and updated on the base table.

Another way would be to maintain tables that noted the number of %APPROVED% comments and held an indicator to say what the last comment was. These could be maintained by some automatic rules/procedures based on inserts into the comments table.

Just a couple of ways you could keep things simpler. Not too sure if they would improve or hurt performance though.

Jim