Find ticketnumber by ost_thread_entry (SQL)

Rickymundo

Hi, how can I find the corresponding ticket number from ost_thread_entry?

I've done a search on +---------------+------------- | Field | Type +---------------+------------- | ticket_id | int unsigned | ticket_pid | int unsigned | number | varchar(20) | user_id | int unsigned | user_email_id | int unsigned | status_id | int unsigned | dept_id | int unsigned | sla_id | int unsigned | topic_id | int unsigned | staff_id | int unsigned | team_id | int unsigned | email_id | int unsigned | lock_id | int unsigned | flags | int unsigned | sort | int unsigned | ip_address | varchar(64) | source | enum('Web',' | source_extra | varchar(40) | isoverdue | tinyint unsigned | isanswered | tinyint unsigned | duedate | datetime | est_duedate | datetime | reopened | datetime | closed | datetime | lastupdate | datetime | created | datetime | updated | datetime +---------------+------------- the body in ost_thread_entry and it came back with this:
------------------------------+------+-----+---------+----------------+
| Null | Key | Default | Extra |
------------------------------+------+-----+---------+----------------+
| NO | PRI | NULL | auto_increment |
| YES | MUL | NULL | |
| YES | | NULL | |
| NO | MUL | 0 | |
| NO | | 0 | |
| NO | MUL | 0 | |
| NO | MUL | 0 | |
| NO | MUL | 0 | |
| NO | MUL | 0 | |
| NO | MUL | 0 | |
| NO | MUL | 0 | |
| NO | | 0 | |
| NO | | 0 | |
| NO | | 0 | |
| NO | | 0 | |
| NO | | | |
Email','Phone','API','Other') | NO | | Other | |
| YES | | NULL | |
| NO | | 0 | |
| NO | | 0 | |
| YES | MUL | NULL | |
| YES | | NULL | |
| YES | | NULL | |
| YES | MUL | NULL | |
| YES | | NULL | |
| NO | MUL | NULL | |
| NO | | NULL | |
------------------------------+------+-----+---------+----------------+

But I cannot match it with a ticketnumber to find the ticket. Can someone please help me?

KevinTheJedi

Rickymundo https://docs.osticket.com/en/latest/Developer%20Documentation/Database%20ERDs.html

Rickymundo

Hi Kevin,
Thanks but my sql knowledge isn't that good. Is there an easier solution or perhaps can someone (you?) compose the SQL statement for me?

ntozier

From a simple step by step view this would do it from Ticket number to thread.
SELECT ticket_id from ost_ticket WHERE NUMBER='TICKETNUMBER';
SELECT id from ost_thread WHERE object_id=ticket_id;
SELECT body FROM ost_thread_entry WHERE thread_id=id

TICKETNUMBER would be a valid ticket number
ticket_id in query two is the result of query one.
id in query three is the result of query two.

so I imagine the reverse would be something like:
SELECT id from ost_thread WHERE object_id=thread_id;
SELECT number FROM ost_ticket WHERE ticket_id=id;

thread_id in query one is the thread that you identified in your question.
id in query two is the result of query 1.