MySQL count of count, use result from one table with another

Question

I'm here to ask an implementation of this question MYSQL count of count?

My issue is to put in relation the result I've achieved pulling out results from one table, using them to query another table of the same DB (sorry, I'm not strong with xySQL).

I have one MySQL database named ticketsdb and I need to extract data from table1 (tickettb) basing on a count made on table2 (poststb)

Scenario: To be more precise it happen that posts in the tickets have replies and I need to get out of poststb table, all the tickets which counts only 2 posts. From that result, I need to extract all the corresponding tickets from the tickettb table. As you may guess, poststb, contains only the ticket identification, the date of that post, the subject and the message, every other user information is in the tickettb.

So far, I succeeded (thank to the above post) to pull out the posts with two replies from the poststb, but since they enter in the game the aliases, I'm stuck.

The code is this

SELECT PostsOfTheGivenTicket, TicketIdentification 
FROM (SELECT posttb.ticket_id, COUNT(posttb.ticket_id) AS 
             PostsOfTheGivenTicket, posttb.ticket_id as TicketIdentification 
      FROM posttb GROUP BY posttb.ticket_id 
      HAVING PostsOfTheGivenTicket= 2) AS TemporaryTable

(maybe it is not the best choice for my goal, so thank you for any confirmation)

Anyway, tested, it returns all the 83 tickets with 2 messages only

The result of the above query is the following one

 PostsOfTheGivenTicket    TicketIdentification 
 2                        1234
 2                        1451
 2                        1526

and looks like it is assigned to the temporay/aliased table TemporaryTable (is it? how to re-use it?)

In the tickettb table I have one column which name has been set as IdTicket which stores the same values

IdTicket  UserName UserPhone UserEmail etc etc
1234
....
1451
....
1526

My goal would be to pull out (finally on the web server) all the tickettb details relative to the matching tickets, in other words

SELECT from tickettb every ticket with IdTicket equal to TicketIdentification

Is it possible to nest the above query into another that achieves the goal?

While the final goal, will turn to select and delete part of the tickets with 2 messages, e.g. only the ones older than 10 days.

score 1 · Accepted Answer · answered Jan 12 '15 at 12:22

1

SELECT * FROM ticketb WHERE IdTicket IN
(
      SELECT ticket_id
      FROM posttb 
      GROUP BY ticket_id 
      HAVING COUNT(ticket_id) = 2
)

answered Jan 12 '15 at 12:22

Taras Velykyy

1,781
1
16
30

So I was running on a totally wrong path and I see that I was missing an fundamental piece of knowledge :-|. Thank you. – Robert Jan 12 '15 at 12:29
No problem. You can find some useful information about subquries here: http://sql-ex.ru/help/select7.php#where – Taras Velykyy Jan 12 '15 at 12:39
1

Really exhaustive, comprehensive and useful link! Bookmarked. – Robert Jan 13 '15 at 05:32

MySQL count of count, use result from one table with another

1 Answers1