CODEWITHSUNDEEP
mysqlsqljoininner-join
carlbenson
carlbenson

Reputation: 3207

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, 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`;

Upvotes: 2

Views: 400

Answers (5)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115550

SELECT *
FROM Pages
   INNER JOIN Pages2Data
      ON Pages.PageID = Pages2Data.PageID
   INNER JOIN Data
      ON Pages2Data.DataID = Data.DataID
WHERE DataWord LIKE 'word%'

Upvotes: 0

Abhay
Abhay

Reputation: 6645

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%';

Upvotes: 1

HLGEM
HLGEM

Reputation: 96570

SELECT * FROM `Pages`     
INNER JOIN `Pages2Data`
   ON `Pages`.`PageID`=`Pages2Data`.`PageID`
INNER JOIN `Data` ON `Data`.`DataID`= `Pages2Data`.`DataID`
WHERE `DataWord` LIKE 'word%';

Upvotes: 2

Sahal
Sahal

Reputation: 4136

You can check 'Like' in where clause.

SELECT * FROM 
           `Pages` 
INNER JOIN 
           `Pages2Data` ON  (`Pages`.`PageID`=`Pages2Data`.`PageID`)
WHERE
            `DataWord` LIKE 'word%'

Upvotes: 0

JellyBelly
JellyBelly

Reputation: 2431

try this

SELECT * FROM `Pages` p, `Pages2Data` p2d, `Data` d
WHERE p.`PageID` = p2d.`PageID`
AND p2d.`DataID` = d.`DataID`
AND `DataWord` LIKE 'word%'

Upvotes: 0

Related Questions