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:

1. the join condition should be next to the ON clause
2. 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%'