SQL: How do I write this tricky piece?

Lt. Manlove · 13 Sep 2007 at 18:31

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

Lt. Manlove · 14 Sep 2007 at 10:31

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

Lt. Manlove · 14 Sep 2007 at 11:22

Code:

+-------------+-----------+------+-----+-------------------+----------------+
| 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)

Code:

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.

Code:

		$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!

Lt. Manlove · 14 Sep 2007 at 12:34

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.

Lt. Manlove · 14 Sep 2007 at 13:25

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