Checking the existence of values ​​(values) in the table (sub) and returning the result "boolean"

Question

I want to create a query in MySQL in the order table and check if it has a reservation identifier, if it does not have a booking_id identifier, it should be available in every way in the invoice table.

I want the return value to be boolean in one field.

Took the given example in

• Case ID # 1 I expect immediate true because it is available

• Case with identifier # 2 I expect that in the invoice table there will be a “slowed down” value, since non- all invoices have booking_id, it should return only true if invoice number 3 is actually a reservation identifier, that is, everything invoices have booking_id when order is not specified.

I tried several ways, but still couldn’t and I don’t even know how best to deal with it.

Thanks for your input in advance!

Table:

|----+------------+

| id | booking_id |

|----+------------+

|  1 |        123 |

|  2 |       NULL |

|----+------------+

Table score:

+----+----------+------------+

| id | order_id | booking_id |

+----+----------+------------+

|  1 |        1 |        123 |

|  2 |        2 |        124 |

|  3 |        2 |       NULL |

+----+----------+------------+

Scheme

CREATE TABLE IF NOT EXISTS `invoice` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `order_id` int(11) NOT NULL,
  `booking_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) 

CREATE TABLE IF NOT EXISTS `order` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `booking_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1;

Answer 1

If I understand you correctly, this is a basic request for your request:

SELECT
    O.id
    , SUM(CASE WHEN I.booking_id IS NOT NULL THEN 1 ELSE 0 END) AS booked_count
    , COUNT(1) AS total_count
    , CASE WHEN SUM(CASE WHEN I.booking_id IS NOT NULL THEN 1 ELSE 0 END) = COUNT(1) THEN 1 ELSE 0 END AS has_all_bookings
FROM
    `order` O
    LEFT JOIN invoice I
        ON O.id = I.order_id
GROUP BY
    O.id

, , COUNT (1) CASE (COUNT (1) = 0)

Answer 2

, , - . , ( ). COUNT GROUP BY SUBSELECT, MySQL ( 5.1.73-1).

:

SELECT o.*
  , (booking_id IS NOT NULL) AS order_booked
  , (NOT EXISTS (SELECT id FROM `invoice` WHERE order_id=o.id AND booking_id IS NULL)) AS invoices_all_booked
FROM `order` o

, :

SELECT o.*
  , (booking_id IS NOT NULL OR NOT EXISTS (SELECT id FROM `invoice` WHERE order_id=o.id AND booking_id IS NULL)) AS booked
FROM `order` o

Answer 3

, ,

   create view booked_view as 
    select `order`.id as order_id
    , 
      case when booking_id > 0 then true
            when exists (SELECT id FROM invoice WHERE order_id=`order`.id AND invoice.booking_id IS NULL) then true
      else false
    end  as booked
    from `order` ;

, ""

   select o.id, booked from `order` o
        join booked_view on (o.id = booked_view.order_id)