Dragonturtle Dragonturtle - 6 months ago 14
MySQL Question

MySQL difficult query among many tables in relational database

I'd like to ask for your help with a query I've been struggling with for hours now. I will only insert the information about them that we need to solve the problem.

1.) The data structure:



Basically, I have four tables I need for this query.


  • Table1: tickets. Contains rows about certain events that are
    ticketed. Has the attributes: t_id, atNight.

  • Table2: ticketintervals. How long has the ticket been going on.
    Attr.: ti_id, t_id, startDate, endDate

  • Table3: ticketassignments. To what person(s) the ticket is
    assigned to, connection table. Attr.: ta_id, person_id,
    ti_id

  • Table4: events. It contains various events, ones that happen at
    night, are ticketed for sure. Attr.: e_id, person_id,
    happenedAtNight, start_date, end_date



2.) So, I have to make a query which lists all the Events that happened at night AND doesnt have a ticket belonging to them with the same interval as in ticketintervals.



To do this I need to consider the following:


  1. events table has its "happenedAtNight" set to 1.

  2. events table's person_id leads to the ticketintervals table, through this I can compare the dates.

  3. While comparing the dates I must consider that the ticket the ticketinterval belongs to is atNight=1.



3.) What I have tried: I could write down my many attempts but the one I found closest to the solution is this:



select * from tickets t, ticketintervals tint, ticketassignments t_ass, events e
where t.atNight = 1
and t.t_id = tint.t_id
and tint.ti_id = t_ass.ti_id
and t_ass.person_id = e.person_id
and e.happenedAtNight = 1
and date_format(tint.startDate,'%Y-%m-%d') != date_format(e.start_date,'%Y-%m-%d')
and date_format(tint.endDate,'%Y-%m-%d') != date_format(e.end_date,'%Y-%m-%d')
;


But this will pair all ticketintervals that are different from the event's date to the date, because there can be tickets that are atNight and don't belong to an event, but belong to a person that belongs to an event. So this way I seem to also get tickets that are on their own, have their intervals, but I only want events to appear when there are no tickets that belong to their time-interval.

The point, in really short now: I want to only see the events at night that dont have tickets to them.

I'm absolutely confused as to how to approach this, I've looked at INNER JOIN and many other ways but I can't seem to grasp how to connect this logic to a MySQL way.

Thank you for your time reading all of this, hope to learn something from this,
Eryse

Answer

Since you're going to list down the events, you'll have to query this table first, then join the other tables you'll need for filtering:

SELECT e.* FROM events e
    LEFT JOIN ticketassignments ta ON e.person_id = ta.person_id
    LEFT JOIN ticketintervals ti ON ta.ti_id = ti.ti_id
    LEFT JOIN tickets t ON ti.t_id = t.t_id

If you need other table's info in the result, just add it in the SELECT statement, eg.:

SELECT e.*, ta.person_id, ti.*

Then filter the events that only happened at night

WHERE e.happenedAtNight = 1

Then make sure the dates from the ticket interval and the event will be compared only to the tickets that also happened at night. Don't compare dates when ticket.atNight = 0, but compare it if ticket.atNight = 1:

AND (t.atNight = 0 OR (t.atNight = 1 
    AND DATE_FORMAT(ti.startDate,'%Y-%m-%d') != DATE_FORMAT(e.start_date,'%Y-%m-%d')
    AND DATE_FORMAT(ti.endDate,'%Y-%m-%d') != DATE_FORMAT(e.end_date,'%Y-%m-%d')
));

Here's the resulting SQL:

SELECT e.* FROM events e
    LEFT JOIN ticketassignments ta ON e.person_id = ta.person_id
    LEFT JOIN ticketintervals ti ON ta.ti_id = ti.ti_id
    LEFT JOIN tickets t ON ti.t_id = t.t_id
WHERE e.happenedAtNight = 1
    AND (t.atNight = 0 OR (t.atNight = 1 
        AND DATE_FORMAT(ti.startDate,'%Y-%m-%d') != DATE_FORMAT(e.start_date,'%Y-%m-%d')
        AND DATE_FORMAT(ti.endDate,'%Y-%m-%d') != DATE_FORMAT(e.end_date,'%Y-%m-%d')
    ));