MySQL query returns rows it shouldn't

Question

I'm facing a problem with a query.

I'm using phpMyAdmin and MySQL.

I'm trying to make a report of all my active clients (in ___Kardex where KDX_Status='active') and :

• count the number of booking they made (in ___Bookings where BOO_Status!='cancel').
• count the number of night they passed (in ___Bookings where BOO_Status!='cancel').

For clarity, here is a sample dataset

CREATE TABLE `___Bookings` (
  `BOO_Id` int(10) NOT NULL AUTO_INCREMENT,
  `BOO_HotelId` varchar(20) NOT NULL,
  `BOO_ClientId` int(10) NOT NULL,
  `BOO_CompanyId` int(10) NOT NULL,
  `BOO_BillingId` int(10) NOT NULL,
  `BOO_DateCI` date NOT NULL,
  `BOO_DateCO` date NOT NULL,
  `BOO_Status` enum('confirmed','notconfirmed','option','cancel','checkin','checkout') NOT NULL,
  UNIQUE KEY `BOO_Id` (`BOO_Id`),
  KEY `id` (`BOO_Id`)
) ENGINE=MyISAM AUTO_INCREMENT=73 DEFAULT CHARSET=utf8;

INSERT INTO `___Bookings` VALUES 
(70,'cus_CNHLMiMOzP5cuM',18,0,30,'2018-03-07','2018-03-12','confirmed'),
(71,'cus_CNHLMiMOzP5cuM',61,62,0,'2018-03-01','2018-03-02','cancel'),
(72,'cus_CNHLMiMOzP5cuM',19,0,0,'2018-03-04','2018-03-06','confirmed'),
(73,'cus_CNHLMiMOzP5cuM',61,0,0,'2018-03-01','2018-03-09','notconfirmed'),
(74,'cus_CNHLMiMOzP5cuM',61,0,0,'2018-03-10','2018-03-11','notconfirmed'),
 (75,'cus_CNHLMiMOzP5cuM',19,62,63,'2018-03-10','2018-03-21','option');

CREATE TABLE `___Hotels` (
  `HOT_HotelId` varchar(20) NOT NULL,
  `HOT_AutoLabel_VIP_Bookings` tinyint(4) NOT NULL,
  `HOT_AutoLabel_VIP_Nights` tinyint(4) NOT NULL,
  `HOT_AutoLabel_Regular_Bookings` tinyint(4) NOT NULL,
  `HOT_AutoLabel_Regular_Nights` tinyint(4) NOT NULL,
  `HOT_Status` enum('active','inactive','pending') NOT NULL,
  PRIMARY KEY (`HOT_HotelId`),
  UNIQUE KEY `HOT_HotelId` (`HOT_HotelId`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `___Hotels` VALUES 
('cus_CNHLMiMOzP5cuM', 10, 15, 20, 25, 'active');

CREATE TABLE `___Kardex` (
  `KDX_Id` int(10) NOT NULL AUTO_INCREMENT,
  `KDX_HotelId` varchar(20) NOT NULL,
  `KDX_Type` enum('client','company','billing') NOT NULL,
  `KDX_Status` enum('active','inactive') NOT NULL,
  UNIQUE KEY `KDX_Id` (`KDX_Id`),
  KEY `id` (`KDX_Id`)
) ENGINE=MyISAM AUTO_INCREMENT=63 DEFAULT CHARSET=utf8;

INSERT INTO `___Kardex` VALUES 
(18,'cus_CNHLMiMOzP5cuM','client','active'),
(19,'cus_CNHLMiMOzP5cuM','client','active'),
(30,'cus_CNHLMiMOzP5cuM','billing','active'),
(61,'cus_CNHLMiMOzP5cuM','client','active'),
(62,'cus_CNHLMiMOzP5cuM','company','inactive'),
(63,'cus_CNHLMiMOzP5cuM','company','active'),
(91,'cus_CNHLMiMOzP5cuM','company','active'),
(92,'cus_CNHLMiMOzP5cuM','company','active');

...and my best effort to date...

SELECT KDX_Id, KDX_Type,
  (
    SELECT COUNT(BOO_Id) 
    FROM ___Bookings 
    WHERE BOO_Status!='cancel' 
    AND (
      KDX_Id = ___Bookings.BOO_ClientId 
      OR KDX_Id = ___Bookings.BOO_CompanyId 
      OR KDX_Id = ___Bookings.BOO_BillingId 
        )
  ) AS nb_bookings,
  ( 
    SELECT SUM(DATEDIFF(___Bookings.BOO_DateCO, ___Bookings.BOO_DateCI)) 
    FROM ___Bookings 
    WHERE BOO_Status!='cancel' 
    AND (
        KDX_Id = ___Bookings.BOO_ClientId 
        OR KDX_Id = ___Bookings.BOO_CompanyId 
        OR KDX_Id = ___Bookings.BOO_BillingId
        )
  ) AS nb_nights, 
  HOT_HotelId, 
  HOT_AutoLabel_VIP_Bookings, 
  HOT_AutoLabel_VIP_Nights, 
  HOT_AutoLabel_Regular_Bookings, 
  HOT_AutoLabel_Regular_Nights 
FROM ___Kardex 
JOIN ___Hotels 
ON ___Kardex.KDX_HotelId = ___Hotels.HOT_HotelId 
JOIN ___Bookings 
ON ___Kardex.KDX_HotelId = ___Bookings.BOO_HotelId 
WHERE KDX_Status='active' 
AND HOT_Status='active' 
GROUP BY KDX_Id

And SQLFiddle of same:

http://sqlfiddle.com/#!9/67775f/1

The desired output should be like in my SQLFiddle above except the row 91 and 92 because I do not have any bookings or nights to show for these two entries.

Actually, the query returns me these rows with NULL or 0 entries.

expected results

| KDX_Id | KDX_Type | nb_bookings | nb_nights |        HOT_HotelId | HOT_AutoLabel_VIP_Bookings | HOT_AutoLabel_VIP_Nights | HOT_AutoLabel_Regular_Bookings | HOT_AutoLabel_Regular_Nights |
|--------|----------|-------------|-----------|--------------------|----------------------------|--------------------------|--------------------------------|------------------------------|
|     18 |   client |           1 |         5 | cus_CNHLMiMOzP5cuM |                         10 |                       15 |                             20 |                           25 |
|     19 |   client |           2 |        13 | cus_CNHLMiMOzP5cuM |                         10 |                       15 |                             20 |                           25 |
|     30 |  billing |           1 |         5 | cus_CNHLMiMOzP5cuM |                         10 |                       15 |                             20 |                           25 |
|     61 |   client |           2 |         9 | cus_CNHLMiMOzP5cuM |                         10 |                       15 |                             20 |                           25 |
|     63 |  company |           1 |        11 | cus_CNHLMiMOzP5cuM |                         10 |                       15 |                             20 |                           25 | 

Any help will be appreciated.

Thanks.

Answer 1

Here i made certain changes to your query:

SELECT KDX_Id, KDX_Type,nb_bookings,nb_nights,
  HOT_HotelId, 
  HOT_AutoLabel_VIP_Bookings, 
  HOT_AutoLabel_VIP_Nights, 
  HOT_AutoLabel_Regular_Bookings, 
  HOT_AutoLabel_Regular_Nights 
FROM (
    SELECT KDX_Id, KDX_Type,COUNT(BOO_Id) AS nb_bookings,SUM(DATEDIFF(___Bookings.BOO_DateCO, ___Bookings.BOO_DateCI)) AS nb_nights,
    ___Kardex.KDX_HotelId
    FROM ___Bookings 
    INNER JOIN ___Kardex
    WHERE BOO_Status!='cancel' AND KDX_Status='active' 
    AND (
      KDX_Id = ___Bookings.BOO_ClientId 
      OR KDX_Id = ___Bookings.BOO_CompanyId 
      OR KDX_Id = ___Bookings.BOO_BillingId 
        )
        GROUP BY KDX_Id
 ) aa 
JOIN ___Hotels ON aa.KDX_HotelId = ___Hotels.HOT_HotelId  AND HOT_Status='active'

This is giving the result as you want. Please let me know if it works for you!! This is for the case of disabling 'only_full_group_by' sql_mode.

Answer 2

I've rewritten the query so you don't need GROUP BY to filter out duplicates.

Query

SELECT 
   kardex_bookings.KDX_id
 , kardex_bookings.KDX_type
 , kardex_bookings.nb_bookings
 , kardex_bookings.nb_nights
 , hotels.HOT_HotelId
 , hotels.HOT_AutoLabel_VIP_Bookings
 , hotels.HOT_AutoLabel_VIP_Nights
 , hotels.HOT_AutoLabel_Regular_Bookings
 , hotels.HOT_AutoLabel_Regular_Nights

FROM ( 

  SELECT 
      kardex.KDX_id    
    , kardex.KDX_HotelId
    , kardex.KDX_type
    , kardex.KDX_Status
    , (
        SELECT
          COUNT(bookings.BOO_Id)
        FROM 
          ___Bookings bookings
        WHERE
            bookings.BOO_Status != 'cancel'
          AND (
              kardex.KDX_Id = bookings.BOO_ClientId 
            OR
              kardex.KDX_Id = bookings.BOO_CompanyId 
            OR
              kardex.KDX_Id = bookings.BOO_BillingId 
          )

      ) AS nb_bookings

    , ( 
        SELECT 
          SUM(DATEDIFF(bookings.BOO_DateCO, bookings.BOO_DateCI)) 
        FROM 
          ___Bookings bookings
        WHERE
          bookings.BOO_Status != 'cancel' 
          AND (
              kardex.KDX_Id = bookings.BOO_ClientId 
            OR
              kardex.KDX_Id = bookings.BOO_CompanyId 
            OR
              kardex.KDX_Id = bookings.BOO_BillingId 
          )    
      ) AS nb_nights    
  FROM 
    ___Kardex kardex     
) 
 AS kardex_bookings
INNER JOIN 
 ___Hotels hotels
ON 
 kardex_bookings.KDX_HotelId = hotels.HOT_HotelId

WHERE 
   kardex_bookings.KDX_Status = 'active'
 AND  
 # filter out non-bookings
   kardex_bookings.nb_bookings != 0
 AND
   kardex_bookings.nb_nights IS NOT NULL
 AND
   hotels.HOT_Status = 'active'

ORDER BY 
 kardex_bookings.KDX_Id ASC

Results

| KDX_id | KDX_type | nb_bookings | nb_nights |        HOT_HotelId | HOT_AutoLabel_VIP_Bookings | HOT_AutoLabel_VIP_Nights | HOT_AutoLabel_Regular_Bookings | HOT_AutoLabel_Regular_Nights |
|--------|----------|-------------|-----------|--------------------|----------------------------|--------------------------|--------------------------------|------------------------------|
|     18 |   client |           1 |         5 | cus_CNHLMiMOzP5cuM |                         10 |                       15 |                             20 |                           25 |
|     19 |   client |           2 |        13 | cus_CNHLMiMOzP5cuM |                         10 |                       15 |                             20 |                           25 |
|     30 |  billing |           1 |         5 | cus_CNHLMiMOzP5cuM |                         10 |                       15 |                             20 |                           25 |
|     61 |   client |           2 |         9 | cus_CNHLMiMOzP5cuM |                         10 |                       15 |                             20 |                           25 |
|     63 |  company |           1 |        11 | cus_CNHLMiMOzP5cuM |                         10 |                       15 |                             20 |                           25 |

demo

http://sqlfiddle.com/#!9/67775f/56