user3100278 user3100278 - 2 months ago 7
MySQL Question

Long cascaded MySQL query

I have a query the is linked to four depth tables:

This is an example:

CREATE TABLE `TableA` (
`ID` int(11) NOT NULL,
`ValueA` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `TableA` (`ID`, `ValueA`) VALUES
(1, 20),
(2, 30);

CREATE TABLE `TableB` (
`ID` int(11) NOT NULL,
`ValueB` int(11) NOT NULL,
`TableA_ID` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `TableB` (`ID`, `ValueB`, `TableA_ID`) VALUES
(1, 40, 2),
(2, 60, 1);

CREATE TABLE `TableC` (
`ID` int(11) NOT NULL,
`ValueC` int(11) NOT NULL,
`TableB_ID` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `TableC` (`ID`, `ValueC`, `TableB_ID`) VALUES
(1, 101, 2),
(2, 102, 1);

CREATE TABLE `TableD` (
`ID` int(11) NOT NULL,
`ValueD` int(11) NOT NULL,
`TableC_ID` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `TableD` (`ID`, `ValueD`, `TableC_ID`) VALUES
(1, 200, 1),
(2, 100, 2);

ALTER TABLE `TableA`
ADD PRIMARY KEY (`ID`);

ALTER TABLE `TableB`
ADD PRIMARY KEY (`ID`),
ADD UNIQUE KEY `TableB_ak_1` (`TableA_ID`),
ADD CONSTRAINT `TableB_TableA` FOREIGN KEY (`TableA_ID`) REFERENCES `TableA` (`ID`);

ALTER TABLE `TableC`
ADD PRIMARY KEY (`ID`),
ADD UNIQUE KEY `TableC_ak_1` (`TableB_ID`),
ADD CONSTRAINT `TableC_TableB` FOREIGN KEY (`TableB_ID`) REFERENCES `TableB` (`ID`);

ALTER TABLE `TableD`
ADD PRIMARY KEY (`ID`),
ADD UNIQUE KEY `TableD_ak_1` (`TableC_ID`),
ADD CONSTRAINT `TableD_TableC` FOREIGN KEY (`TableC_ID`) REFERENCES `TableC` (`ID`);


They are connected with uniq constrains. So basicaly for every ID in TableD I'll have a value from tableA.

Now, what is the right methud for getting this info except for running queris like the followging one:

mysql> select ValueA from TableA where ID = (select TableA_ID from TableB where ID = (select TableB_ID from TableC where ID = (Select TableC_ID from TableD where ID = 1)));
+--------+
| ValueA |
+--------+
| 20 |
+--------+

Answer

You're right, your query with all the subqueries is ridiculous.

SELECT ValueA FROM TableA 
WHERE ID = (SELECT TableA_ID FROM TableB WHERE ID = 
            (SELECT TableB_ID FROM TableC WHERE ID = 
             (SELECT TableC_ID FROM TableD WHERE ID = 1)));

You really need to learn to use JOIN if you're going to use SQL. JOIN is a basic operation. Avoiding JOIN in SQL would be like avoiding a while loop in most other languages.

Here's the equivalent query using JOIN syntax.

SELECT ValueA
FROM TableA
JOIN TableB ON TableA.ID = TableB.TableA_ID
JOIN TableC ON TableB.ID = TableC.TableB_ID
JOIN TableD ON TableC.ID = TableD.TableC_ID
WHERE TableD.ID = 1;

Here's a demo: http://sqlfiddle.com/#!9/34503/2