0
0 Comments

I have 3 tables in my MySQL database.

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `fullname` varchar(30) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

CREATE TABLE `softs` (
  `Id` int(11) NOT NULL,
  `Title` varchar(50) NOT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `plans` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `SoftId` int(11) NOT NULL,
  `Explain` varchar(500) NOT NULL,
  `Rating` int(11) NOT NULL,
  `Done` tinyint(4) NOT NULL,
  `NowIs` datetime NOT NULL,
  `DoneTime` datetime NOT NULL,
  `note` varchar(30) NOT NULL,
  `userid` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `SoftId` (`SoftId`),
  KEY `userid` (`userid`),
  CONSTRAINT `plans_ibfk_1` FOREIGN KEY (`SoftId`) REFERENCES `softs` (`Id`),
  CONSTRAINT `plans_ibfk_2` FOREIGN KEY (`userid`) REFERENCES `users` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;

I want first to retrieve undone records and order them by ‘rating’ column in ascending order, followed by already done plans, ordered by ‘DoneTime’ column in descending order. Therefore I wrote the query below.

(SELECT a.id, b.title, a.`Explain`, a.Done, a.DoneTime, a.note, c.fullname, a.rating 
    FROM plans as a 
    inner join softs as b ON b.Id = a.SoftId 
    inner join users as c ON c.id = a.userid 
    where(a.done = 0) order by a.Rating asc)
union
(SELECT a.id, b.title, a.`Explain`, a.Done, a.DoneTime, a.note, c.fullname, a.rating 
    FROM plans as a 
    inner join softs as b ON b.Id = a.SoftId 
    inner join users as c ON c.id = a.userid 
    where(a.done = 1) 
    order by a.Donetime desc) ;

When I ran them separately, each works fine, but with "union" records not ordered.

How I can achieve this task with one query?

Anonymous Asked question May 13, 2021