Running a check within a SQL query (Maybe a subquery?)

Question

I have a simple laptop testing booking system with 6 laptops, named Laptop01 to 06 that each have three allocated time slots.

A user is is able to select these time slots if the slot is not booked or if the booking has been cancelled/declined.

While I have working code, I've realised a fatal error that causes a cancelled/declined slot to duplicate.

Let me explain...

• event_information - Holds the booking event information (only ID is needed for this example)
• event_machine_time - This hold all the laptops, with three rows per laptop with the unique timings available to choose from
• event_booking - This holds the actual booking, which then links to another candidate database, not included here

I then run a simple query that joins everything together and (I thought) identifies the booked events:

SELECT machine_laptop, machine_name, B.id AS m_id, C.id AS c_id, C.confirmed AS c_confirmed, C.live AS c_live, 
(C.id IS NOT NULL AND C.confirmed !=2 AND C.live !=0) AS booked
FROM event_information A
INNER JOIN event_machine_time B ON ( 1 =1 ) 
LEFT JOIN event_booking C on (B.id = C.machine_time_id and A.id = C.information_id )
WHERE A.id = :id
ORDER BY `B`.`id` DESC

booked is checking if confirmed isn't 2 - which means the booking has been cancelled/declined (0 - not confirmed, 1 - confirmed) and live is checking for deletion (0 - deleted, 1 - not deleted).

However if a person either gets deleted (live - 0) or cancels/declines (confirmed - 2) then in my front end slot selector dropdown it will add an extra slot as the booked column is still 0, as shown below:

This allows the user to then choose from two slots at the same time, meaning double bookings occur.

I now know that using a Join is the wrong thing to do, and I'm presuming that I need to run a subquery, but I'm not an SQL expert and I would love some help to find examples of similar 'second queries' that I can learn from.

Also apologies if my terminology is wrong.

EDIT:

As requested I've included the output:

Second edit and conclusion:

In the end I managed to craft a solution together using a sub query to remove the cancelled/declined bookings before the output, then use a Group By to only display one of each timing. This most likely isn't the best way, but it worked for me.

SELECT machine_laptop, machine_name, B.id AS m_id, C.id AS c_id, C.confirmed AS c_confirmed, C.live AS c_live, B.start_time AS b_start_time, (
            C.id IS NOT NULL 
            AND C.confirmed !=2
            AND C.live !=0
            ) AS booked
            FROM event_information A
            INNER JOIN event_machine_time B ON (1=1) 
            LEFT JOIN (SELECT * FROM event_booking WHERE confirmed <> '2' AND live <> '0') AS C ON ( B.id = C.machine_time_id AND A.id = C.information_id ) 
            WHERE A.id = :id
            GROUP BY m_id
            ORDER BY machine_name ASC, b_start_time ASC

Thank you for all your input.

Answer 1

Try below :

SELECT machine_laptop, machine_name, B.id AS m_id, C.id AS c_id, C.confirmed 
AS c_confirmed, C.live AS c_live, 
(C.id IS NOT NULL AND C.confirmed !=2 AND C.live !=0) AS booked
FROM event_information A
LEFT JOIN event_booking C ON A.id = C.information_id
RIGHT JOIN event_machine_time B ON B.id = C.machine_time_id
WHERE A.id = :id
ORDER BY `B`.`id` DESC

Answer 2

If you make the event_booking (B) as starting point for your query, you can see that there's no need to use pull all rows and columns from A and C. Intead you can join on matching rows directly. But as I can't even properly grasp what your query is trying to achieve, I have couple of questions first:

While this may work it's actually something that's not under your control nor defined by you. Some more strict mode would politely tell you to specify which aliased table you're referring to in your SELECT, as this

SELECT machine_laptop, machine_name  -- combined with
FROM event_information A 

actually doesn't make sense and the only reason why it's working is that you're leveraging on MySQL's optimisations. In addition to that you're trying to do table joins in a mixed mode (meaning that you use both JOIN and WHERE tA.colX=tB.colY methods. This makes it really difficult to follow.

INNER JOIN event_machine_time B ON ( 1 =1 ) 

Um? What exactly is the e purpose of this? As far as I can tell this will only cause it to JOIN both full tables, only to later filter the result using WHERE.

Furthermore, are you even using primary keys? Your condition includes C.id IS NOT NULL while primary keys can't even contain NULLs (as NULL is third boolean state in SQL land. There is True, False, and Null (meaning Undefined, which obviously couldn't be used in primary key, as primary key must be unique and Undefined value can be anything or nothing - ergo it's violating the uniqueness requirement). So I'm assuming you're actually using this NULL check because the temp table during JOIN seems to contain them?

EDIT: Try to split this into two parts, where you first join 2 tables, and then join third table with the result.

I suggest you go briefly over What is the difference between "INNER JOIN" and "OUTER JOIN"? - as this is pretty great post and clarifies many aspects.

For startest I'd go with something like:

SELECT
    <i.cols>,
    <b.cols>,
    <mt.cols>, 
    IF(b.confirmed !=2 AND b.live !=0, True, False) sa booked
FROM 
   event_booking b 
LEFT JOIN 
   event_information i ON b.information_id = i.id
LEFT JOIN 
   event_machine_time mt ON b.machine_time_id = mt.id
WHERE <conditions>

Later I'd change LEFT JOIN into something more appropriate. However bear in mind that INNER JOIN is only useful if you're 100% sure that there rows returned from joined table columns are unique.

Can there even be 1:n, n:1 relationship between i and b tables? I'd assume there couldn't be multiple bookings to same event info (n:1), nor there'd be so that event information is the same for multiple events ? (1:n)