Cross Referencing Multiple Tables

Question

What I was trying to do is to get data from multiple tables, supposed that I have the following results in my query:

The numbers in the column ticket_item_type represents certain table. For example, 2 is for tbl_company and 3 is for tbl_lease. Then the details represents the id of a certain record in that table.

Suppose that I want to get the title of those records using ticket_item_type and details. Is it possible to embed it to the results? Or should I make separate queries for each.

I know JOIN, but I is it only for single table?

Here's my MYSQL query for the image above:

SELECT *
FROM
  (SELECT *
   FROM ticket_items
   WHERE hs_customer = 1
     AND ticket IN
       (SELECT id
        FROM tickets
        WHERE hs_customer='1'
          AND ticket_status = 'dispatch_reviewed')
     AND ticket IN
       (SELECT ticket
        FROM ticket_items
        WHERE ticket_item_type = 5
          AND details = '159')) AS TB1
WHERE ticket_item_type IN (3,
                           2,
                           8)

score 0 · Accepted Answer · answered Nov 05 '15 at 06:26

You could try something like this:

SELECT 
    TB1.*,
    CASE
    WHEN TB1.ticket_item_type = 2 THEN t2.title
    WHEN TB1.ticket_item_type = 3 THEN t3.title
    WHEN TB1.ticket_item_type = 8 THEN t8.title
    ELSE 'NA'
    END as title
FROM
(
   SELECT *
   FROM ticket_items
   WHERE hs_customer = 1
     AND ticket IN (SELECT id FROM tickets WHERE hs_customer='1' AND ticket_status = 'dispatch_reviewed')
     AND ticket IN (SELECT ticket FROM ticket_items WHERE ticket_item_type = 5 AND details = '159')
) AS TB1
LEFT JOIN tbl_company t2 ON TB1.details = t2.id
LEFT JOIN tbl_lease t3 ON TB1.details = t3.id
LEFT JOIN tbl_next t8 ON TB1.details = t8.id
WHERE ticket_item_type IN (3, 2, 8)

However, this is not a design that I would prefer. Without looking at details of your database it's going to be hard to write a query to cover multiple types of ticket_item_type. I hope this query works for you, though.

Cross Referencing Multiple Tables

1 Answers1