CODEWITHSUNDEEP
mysqlunion
Demonslay335
Demonslay335

Reputation: 796

Reference JOINing

I am trying to make a reference system for tickets in the database. The tickets are distinguished as separate "databases" in the primary table, and I have a secondary table that points to this table for other databases to have a reference to another database's tickets.

An example scheme:

`tickets`
-------------------------------------------
| ticket_id | ticket_cid | ticket_database|
-------------------------------------------
|        1 |          1 |               1 |
|        2 |          1 |               2 |
|        3 |          1 |               3 |
-------------------------------------------

`tickets_references`
--------------------------------------
| ref_id | ref_ticket | ref_database |
--------------------------------------
|      1 |          1 |            2 |
|      2 |          3 |            1 |
--------------------------------------

This allows me to keep separate "databases", but then make special cases for one ticket to appear in another database. For this example, each database has one ticket. Ticket #1 will show up in Database #1 exclusively, but then the reference makes it appear in Database #2 as well. Ticket #3 shows up in Database #3, but also shows as a reference in Database #1. Ticket #2 simply shows in Database #2 with no references.

My problem comes to the UNION I am trying to create to pull these referenced tickets into the same results as a typical list of tickets for the database.

For example, my normal query looks like for one database (super simplified from numerous JOINs):

SELECT t.*
FROM tickets
WHERE t.ticket_database = 1

Here are a few attempts I tried, each with syntax errors around either the UNION or WHERE:

SELECT
    (SELECT t.*
    FROM tickets AS t
    )

    UNION ALL

    (SELECT t.*
    FROM tickets_references AS r
    LEFT JOIN tickets
        ON r.ref_ticket = t.ticket_id
    )

WHERE t.ticket_database = 1
ORDER BY t.ticket_opened



SELECT *
    FROM (SELECT t.*
        FROM tickets AS t

        UNION ALL

        SELECT t.*
        FROM tickets_references AS r
        LEFT JOIN tickets AS t
            ON r.ref_ticket = t.ticket_id

    )

WHERE t.ticket_database = 1
ORDER BY t.ticket_opened

It is actually important in my current code structure for the WHERE to be the very last clause if at all possible, as it is appended for various conditions. I also of course need a way to have all of my WHERE clauses apply to both sets, as it should all come in as one result.

Upvotes: 0

Views: 22

Answers (1)

pobrelkey
pobrelkey

Reputation: 5973

Doesn't look to me like you need a UNION:

SELECT DISTINCT t.*
FROM tickets t
LEFT JOIN tickets_references tr ON tr.ref_ticket = t.ticket_id
WHERE t.ticket_database = 1 OR (tr.ticket_database IS NOT NULL AND tr.ticket_database = 1)
ORDER BY t.ticket_opened

Upvotes: 1

Related Questions