Inner Join in ON Clause
I have a MySQL query that is trying to get all the Pages that contain Data like 'word%'. I have a many-to-many table called Pages2Data. It seems that to do this, I need to have an inner join connecting the Pages to the Pages2Data table, and then another inner join connecting Pages2Data to Data.
The following did not work,开发者_JS百科 because the nested SELECT clause can return more than one row. I'm not sure how to fix it though:
SELECT * FROM `Pages`
INNER JOIN `Pages2Data` ON
(`Pages2Data`.`DataID`=(SELECT `DataID` FROM `Data` WHERE `DataWord` LIKE 'word%'))
AND `Pages`.`PageID`=`Pages2Data`.`PageID`;
SELECT * FROM `Pages`
INNER JOIN `Pages2Data`
ON `Pages`.`PageID`=`Pages2Data`.`PageID`
INNER JOIN `Data` ON `Data`.`DataID`= `Pages2Data`.`DataID`
WHERE `DataWord` LIKE 'word%';
First the problems with your query:
- the join condition should be next to the ON clause
- there is no WHERE clause in the outer most query
Fixed query:
SELECT * FROM `Pages`
INNER JOIN `Pages2Data` ON `Pages`.`PageID`=`Pages2Data`.`PageID`
WHERE `Pages2Data`.`DataID`= (SELECT `DataID` FROM `Data` WHERE `DataWord` LIKE 'word%');
Alternative query:
SELECT `PG`.*
FROM `Pages` `PG`
INNER JOIN `Pages2Data` `PD` ON `PD`.`PageID` = `PG`.`PageID`
INNER JOIN `Data` `DA` ON `PD`.`DataID` = `DA`.`DataID`
WHERE `DA`.`DataWord` LIKE 'word%';
try this
SELECT * FROM `Pages` p, `Pages2Data` p2d, `Data` d
WHERE p.`PageID` = p2d.`PageID`
AND p2d.`DataID` = d.`DataID`
AND `DataWord` LIKE 'word%'
You can check 'Like' in where clause.
SELECT * FROM
`Pages`
INNER JOIN
`Pages2Data` ON (`Pages`.`PageID`=`Pages2Data`.`PageID`)
WHERE
`DataWord` LIKE 'word%'
SELECT *
FROM Pages
INNER JOIN Pages2Data
ON Pages.PageID = Pages2Data.PageID
INNER JOIN Data
ON Pages2Data.DataID = Data.DataID
WHERE DataWord LIKE 'word%'
精彩评论