I am trying to get a Count of all Messages in a User’s Trash. (This includes both “Incoming” messages from Others, and “Sent” messages from the User him/herself.)
Here is my query…
SELECT COUNT( pm.id )
FROM pm_recipient AS r
INNER JOIN private_msg AS pm ON pm.id = r.message_id
INNER JOIN member AS m_from ON m_from.id = pm.member_id_from
WHERE r.member_id_to = ?
AND r.deleted_on IS NOT NULL
AND r.purged_on IS NULL
UNION
SELECT COUNT( pm.id )
FROM pm_recipient AS r
INNER JOIN private_msg AS pm ON pm.id = r.message_id
INNER JOIN member AS m_from ON m_from.id = pm.member_id_from
WHERE pm.member_id_from = ?
AND sender_deleted_on IS NOT NULL
AND sender_purged_on IS NULL
Unfortunately, it is giving me these two separate results, and I just wanted ONE RESULT (i.e. 18)…
I’m sticking my neck out on this one, because MySQL isn’t my usual database. If anyone gives you a different answer, chances are that they are right and I am wrong.
That said, the solution would be to simply sum the two rows of your result set. Something like this:
Thanks for the suggestion, but phpMyAdmin didn’t like it, and spit out…
MySQL said: Documentation
#1248 - Every derived table must have its own alias
Worst case scenario, I could bind the two Counts to variables in my Prepared Statement, and then add them up in PHP, but it just seems to me like this is not an uncommon problem, and so there must be a way to do this directly in MySQL?!
Looks like you were very close with your first suggestion…
After playing around with your query, it looks like adding “t1” at the end was all that was needed…
SELECT SUM( TrashCount )
FROM (
SELECT COUNT( pm.id ) AS TrashCount
FROM pm_recipient AS r
INNER JOIN private_msg AS pm ON pm.id = r.message_id
WHERE r.member_id_to = ?
AND r.deleted_on IS NOT NULL
AND r.purged_on IS NULL
UNION
SELECT COUNT( pm.id ) AS TrashCount
FROM pm_recipient AS r
INNER JOIN private_msg AS pm ON pm.id = r.message_id
WHERE pm.member_id_from = ?
AND sender_deleted_on IS NOT NULL
AND sender_purged_on IS NULL
) t1
With that change, I get these results…
SUM(TrashCount)
18
Thanks for almost getting me there!! :tup:
BTW, if there is a better way to get the results that I want, then someone please speak up!!
That’s right. As the error message indicates, I was missing an alias for the “derived table”, that is, the part of the query between the outer parentheses. I don’t know why the alias is necessary, but it does seem to be required.
Glad you’ve got it working. There might well be a better way, but I’ll have to leave it to someone else to show it.
I don’t know why the alias is necessary, but it does seem to be required.
Indeed, it “just is” required. In this case there is only one source for the data so the name is not used, but it is still required, for consistency’s sake I guess.
I don’t know wether the UNION is a performance choice, but you could try just adding the subqueries together:
SELECT (SELECT 1) + (SELECT 2);
and ofcourse you could just add the two sets of conditions together:
WHERE
(r.member_id_to = ?
AND r.deleted_on IS NOT NULL
AND r.purged_on IS NULL
)
OR
(pm.member_id_from = ?
AND sender_deleted_on IS NOT NULL
A benchmark will tell which )
i recall at one time (earlier version?) mysql would totally barf on performance with an OR in the WHERE clause like that, which is why i’ve always written it as a UNION
i recall at one time (earlier version?) mysql would totally barf on performance with an OR in the WHERE clause like that, which is why i’ve always written it as a UNION
Very true, OR clauses can be a serious performance problem in many databases, but I find the performance depends very much on the phase of the moon, the color of the screensaver and probably the distribution of the options in the OR clause within the column too.
WHERE (
r.member_id_to = ?
AND [i]other conditions[/i]
)
[B]OR [/B](
pm.member_id_from = ?
AND [i]other conditions[/i]
)
both r.member_id_to and pm.member_id_from could have indexes but unless the database engine makes two passes at the data, it’s going to have to use a table scan to examine both columns
