Technical Latest content Contact forms Forums Blogs Resources

SQL command: get attachment info for a ticket SuperOffice 10.x

lock
push_pin
done
Answered
1
1

In the database documentation there is a relation described:

ticket - ej_message - ticket_attachment - attachment 

Problem: I have a lot of tickets and attachments, but table ej_message is empty. But in the CS interface all data is shown the right way. I tried to join like below, but that seems to be the wrong relation. What is the right way?

select  ....

from crm7.ticket t1
left join crm7.TICKET_ATTACHMENT ta1 on t1.id = ta1.message_id
left join crm7.ATTACHMENT at1 on ta1.attachment_id = at1.id

 

Thanks for some info

CK
Christian Kogel
23 h, 45 m ago | 02:21 PM

All Replies (1)

message_id of the ticket_attachment table points to the ej_message table.

Pretty sure you can't directly link a attachment to a ticket, only through a message.

Sample SQL;

SELECT * FROM crm.ATTACHMENT
LEFT JOIN crm.TICKET_ATTACHMENT
ON crm.TICKET_ATTACHMENT.attachment_id = crm.ATTACHMENT.id

LEFT JOIN crm.EJ_MESSAGE
ON crm.EJ_MESSAGE.id = crm.TICKET_ATTACHMENT.message_id

WHERE crm.EJ_MESSAGE.ticket_id = 15464
David Hollegien
23 h, 37 m ago | 02:29 PM

Add reply