Proper SQL Query for Displaying the name instead of ID Reportico

Question

I have 3 tables, Tickets, Category, and Sub Category. In Yii2 Reportico in order to generate reports you need to configure the SQL Query.

SELECT id, time_start, time_end, details, room_no, employee_id, category_id, sub_cat_id  FROM tickets

This is my SQL Query for getting data in tickets table, instead of displaying the category_id and sub_cat_id. I want to display the category_name and sub_category, what is the proper sql syntax?

Answer 1

You can try something like this and see if you get the category name:

SELECT t.id, t.time_start, t.time_end, t.details, t.room_no, t.employee_id, 
c.category_name , s.sub_category
FROM tickets as t inner join Category as c on t.category_id=c.id inner join
sub_cat as s on t.sub_cat_id=s.id and s.category_id=c.id

I'm not aware of field names you have in Category and Sub Category tables, but if it works for Category, try adding Sub Category table in a similar manner, or post the field list of both tables.