Teknisk Latest content Contact forms Forums Blogs Resources

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

lock
push_pin
done
Besvart
1
2

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
22. nov. 2024 | 02:21 p.m.

Alle Svar (2)

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
22. nov. 2024 | 02:29 p.m.
Thanks,

I looked to a wrong database where ej_message was empty. My mistake.

CK
Christian Kogel
25. nov. 2024 | 08:37 a.m.

Legg til svar