Search code examples
mysqlsqldatabaseselectwhere-clause

Why i get wrong result set mysql?

EDIT Attention! i know its looks confusing but answer of steveo
SELECT * from orders Group by orderId having masterId = 29
help me , i just dont undestand how does it work

I dont undestand what is going on , i just get wrong result i have table orders that looks lile this:

CREATE TABLE `orders` (
  `orderId` int NOT NULL AUTO_INCREMENT,
  `userId` int NOT NULL,
  `masterId` int NOT NULL,
  `serviceId` int NOT NULL,
  `startTime` datetime NOT NULL,
  `endTime` datetime NOT NULL,
  `timeSlotId` int DEFAULT NULL,
  `isApplied` tinyint(1) NOT NULL,
  `isDone` tinyint(1) NOT NULL,
  PRIMARY KEY (`orderId`),
  KEY `FK_user` (`userId`),
  KEY `FK_master` (`masterId`),
  KEY `FK_timeSlotOrder` (`timeSlotId`),
  CONSTRAINT `FK_master` FOREIGN KEY (`masterId`) REFERENCES `masters` (`masterId`) ON DELETE CASCADE,
  CONSTRAINT `FK_timeSlotOrder` FOREIGN KEY (`timeSlotId`) REFERENCES `time_slot` (`timeSlotId`) ON DELETE CASCADE,
  CONSTRAINT `FK_user` FOREIGN KEY (`userId`) REFERENCES `user_info` (`userId`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

and now i have two rows in a table : bug mysql

and when i make this select command i get correct result set: Correct result set

but when i specify id of master(masterId column in order table) i get only one result despite there should be two orders enter image description here i just dont undestand why is that happening Thanks

Solution

  • Try this out:

    SELECT *
FROM salon.orders
GROUP BY orderId
HAVING masterId = 29