I have a table named Tasks.
It has columns like: taskID, taskName, projectName etc.
I have another tablecalled TaskDependencies. It has two columns: firstTask, secondTask
Both are foreignkeys referring to taskID from the first column.
In projects one task can be dependent on multiple tasks, and multiple tasks can be dependent on one task.
The TaskDependencies table is to keep data about which tasks are dependent on which tasks.
In a row, the task secondTask referring to is dependent on the task referring to firstTask.
Some task are not dependent in any other tasks, and nor any other tasks is dependent on them. So not all tasks in Tasks table do have a reference in TaskDependencies table.
What I want to achieve is, I want to get all tasks that you can add as dependent to another task with taskID = x.
So I need to get all tasks that is not in TaskDependencies table in same row with the value x.
I have used this to achieve that:
SELECT * FROM Tasks WHERE taskID
NOT IN(SELECT firstTask FROM TaskDependencies WHERE secondTask = :taskID)
AND taskID NOT IN(SELECT secondTask FROM TaskDependencies WHERE firstTask = :taskID)
AND taskID != :taskID AND projectName = :projectName;
And it works if I'm not wrong.
But they mention it's not effective to use IN() functions in MySQL. I tried some methods using JOIN but didn't manage that.
Last thing I tried was this:
SELECT * FROM (SELECT * FROM Tasks
LEFT JOIN TaskDependencies as t1 on t1.firstTask = Tasks.taskID
WHERE NOT t1.firstTask <=> 27 AND t1.firstTask IS NULL) as tasks1
INNER JOIN
(SELECT * FROM Tasks
LEFT JOIN TaskDependencies as t2 on t2.secondTask = Tasks.taskID
WHERE NOT t2.secondTask <=> 27 AND t2.secondTask IS NULL) as tasks2 on tasks1.taskID = tasks2.taskID WHERE 1;
But this doesn't work.
How can I achieve this more effectively?
Here's export of the two tables:
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;
--
-- Database: `stud_v20_keser`
--
-- --------------------------------------------------------
--
-- Table structure for table `TaskDependencies`
--
CREATE TABLE `TaskDependencies` (
`firstTask` int(11) DEFAULT NULL,
`secondTask` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_danish_ci;
--
-- Dumping data for table `TaskDependencies`
--
INSERT INTO `TaskDependencies` (`firstTask`, `secondTask`) VALUES
(24, 27),
(26, 28),
(27, 28),
(25, 23),
(26, 23),
(27, 23);
-- --------------------------------------------------------
--
-- Table structure for table `Tasks`
--
CREATE TABLE `Tasks` (
`taskID` int(11) NOT NULL,
`phaseID` int(11) DEFAULT NULL,
`groupID` int(11) DEFAULT NULL,
`parentTask` int(11) DEFAULT NULL,
`taskName` varchar(45) COLLATE utf8_danish_ci NOT NULL,
`status` int(1) NOT NULL DEFAULT 0,
`projectName` varchar(45) COLLATE utf8_danish_ci DEFAULT NULL,
`timeSpent` int(11) DEFAULT 0,
`estimatedTime` int(11) DEFAULT 0,
`hasSubtask` tinyint(1) NOT NULL DEFAULT 0,
`mainResponsible` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_danish_ci;
--
-- Dumping data for table `Tasks`
--
INSERT INTO `Tasks` (`taskID`, `phaseID`, `groupID`, `parentTask`, `taskName`, `status`, `projectName`, `timeSpent`, `estimatedTime`, `hasSubtask`, `mainResponsible`) VALUES
(23, NULL, 9, NULL, 'Tulle oppgave', 0, 'Noe prosjekt', 0, 32, 1, NULL),
(24, NULL, 9, 23, 'Deloppgave 1', 0, 'Noe prosjekt', 0, 15, 0, NULL),
(25, NULL, 9, 23, 'Deloppgave 2', 0, 'Noe prosjekt', 0, 3, 0, NULL),
(26, NULL, 9, 23, 'deloppgave 3', 0, 'Noe prosjekt', 0, 4, 0, NULL),
(27, NULL, 9, 23, 'Deloppgave 4', 0, 'Noe prosjekt', 0, 6, 0, NULL),
(28, NULL, 9, 23, 'Deloppgave 5', 0, 'Noe prosjekt', 0, 4, 0, NULL);
--
-- Indexes for dumped tables
--
--
-- Indexes for table `TaskDependencies`
--
ALTER TABLE `TaskDependencies`
ADD KEY `First Task FK_idx` (`firstTask`),
ADD KEY `TaskDependencies Second Task FK_idx` (`secondTask`);
--
-- Indexes for table `Tasks`
--
ALTER TABLE `Tasks`
ADD PRIMARY KEY (`taskID`),
ADD KEY `Tasks Project name FK_idx` (`projectName`),
ADD KEY `Tasks phaseID FK_idx` (`phaseID`),
ADD KEY `Tasks groupID FK_idx` (`groupID`),
ADD KEY `Tasks parentTask_idx` (`parentTask`),
ADD KEY `Tasks mainResponsible FK_idx` (`mainResponsible`);
--
-- AUTO_INCREMENT for dumped tables
--
--
-- AUTO_INCREMENT for table `Tasks`
--
ALTER TABLE `Tasks`
MODIFY `taskID` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=29;
--
-- Constraints for dumped tables
--
--
-- Constraints for table `TaskDependencies`
--
ALTER TABLE `TaskDependencies`
ADD CONSTRAINT `TaskDependencies First Task FK` FOREIGN KEY (`firstTask`) REFERENCES `Tasks` (`taskID`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `TaskDependencies Second Task FK` FOREIGN KEY (`secondTask`) REFERENCES `Tasks` (`taskID`) ON DELETE CASCADE ON UPDATE CASCADE;
--
-- Constraints for table `Tasks`
--
ALTER TABLE `Tasks`
ADD CONSTRAINT `Tasks Project name FK` FOREIGN KEY (`projectName`) REFERENCES `Projects` (`projectName`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `Tasks groupID FK` FOREIGN KEY (`groupID`) REFERENCES `Groups` (`groupID`) ON DELETE SET NULL ON UPDATE CASCADE,
ADD CONSTRAINT `Tasks mainResponsible FK` FOREIGN KEY (`mainResponsible`) REFERENCES `Users` (`userID`) ON DELETE SET NULL ON UPDATE CASCADE,
ADD CONSTRAINT `Tasks parentTask FK` FOREIGN KEY (`parentTask`) REFERENCES `Tasks` (`taskID`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `Tasks phaseID FK` FOREIGN KEY (`phaseID`) REFERENCES `Phases` (`phaseID`) ON DELETE SET NULL ON UPDATE CASCADE;
COMMIT;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
You can do it with NOT EXISTS:
SELECT t.*
FROM Tasks t
WHERE t.taskID <> :taskID
AND NOT EXISTS (
SELECT 1 FROM TaskDependencies d
WHERE (d.firstTask, d.secondTask) IN ((t.taskID, :taskID), (:taskID, t.taskID))
)
Or:
SELECT t.*
FROM Tasks t
WHERE t.taskID <> :taskID
AND NOT EXISTS (
SELECT 1 FROM TaskDependencies d
WHERE LEAST(d.firstTask, d.secondTask) = LEAST(t.taskID, :taskID)
AND GREATEST(d.firstTask, d.secondTask) = GREATEST(t.taskID, :taskID)
)
See the demo.