Viewing Thread:
"Multiple MySQL Inner Join with Table Aliases"

Here's a bit of a tricky one. I'm not very good with joins, and this is proving nigh on impossible.

I have two tables, one called `content` and one called `pages`.

`content` has 2 columns - `contentid`, `text`, `languageid`
`pages` has 4 columns = `pageid`, `name`, `title`, `body`

Each of the columns in the `pages` table is an integer, and the values in `name`, `title`, `body` link to the `contentid` column in `content`.

For example, I might have the following data in a row in `pages`:

'1', '1', '2', '3'

and the following rows in `content:

'1', 'Test Name', '1'
'2', 'Test Title, '1'
'3', 'Test Body', '1'

If I do a SELECT * query on `pages`, I get the integers, what I want returned is something like:

'1', 'Test Name', 'Test Title', 'Test Body'

where I can specify the `content`.`languageid` and `pages`.`pageid` to match. Anybody got any ideas how to do it? My best shot so far has been:

SELECT `pages`.`pageid`, `content1`.`text` AS `title` FROM ( `content` AS `content1` INNER JOIN `pages` ON (`content1`.`contentid`=`pages`.`title`)) WHERE `pages`.`pageid`='1' AND `content1`.`languageid`='1'

This returns:

'1', 'Test Title'

But I can't work out how to expand it to return the rest. Please help!

IGNORE :: Nimco = stupid! Hadn't actually bothered calling the query in PHP....

Hmmmmm, my query is now:

SELECT `pages`.`pageid`, `pages`.`block`,
`namedata`.`value` AS `name`,
`titledata`.`value` AS `title`,
`bodydata`.`value`
FROM `pages`
INNER JOIN `content` AS `namedata` ON `namedata`.`contentid`=`pages`.`name` INNER JOIN `content` AS `titledata` ON `titledata`.`contentid`=`pages`.`title`
INNER JOIN `content` AS `bodydata` ON `bodydata`.`contentid`=`pages`.`body`
WHERE `pages`.`pageid`='1' AND `namedata`.`languageid`='1' AND `titledata`.`languageid`='1' AND `bodydata`.`languageid`='1'

Works fine in phpMyAdmin, but PHP itself won't have it. Hmmm. Says invalid resource.

Perfect, you God! Lol. There shouldn't be a problem with a clash cos I use backticks, but I'll change the fieldname anyway.

Several ways you can do it, but I guess the method you're trying to do is...

SELECT pages.pageid,namedata.ctext,titledata.ctext,bodydata.ctext from pages
INNER JOIN content AS namedata ON namedata.contentid=pages.name
INNER JOIN content AS titledata ON titledata.contentid=pages.title
INNER JOIN content AS bodydata ON bodydata.contentid=pages.body

(note: I used ctext instead of text as a field name because of the clash with the datatype)

Here's a bit of a tricky one. I'm not very good with joins, and this is proving nigh on impossible.

I have two tables, one called `content` and one called `pages`.

`content` has 2 columns - `contentid`, `text`, `languageid`
`pages` has 4 columns = `pageid`, `name`, `title`, `body`

Each of the columns in the `pages` table is an integer, and the values in `name`, `title`, `body` link to the `contentid` column in `content`.

For example, I might have the following data in a row in `pages`:

'1', '1', '2', '3'

and the following rows in `content:

'1', 'Test Name', '1'
'2', 'Test Title, '1'
'3', 'Test Body', '1'

If I do a SELECT * query on `pages`, I get the integers, what I want returned is something like:

'1', 'Test Name', 'Test Title', 'Test Body'

where I can specify the `content`.`languageid` and `pages`.`pageid` to match. Anybody got any ideas how to do it? My best shot so far has been:

SELECT `pages`.`pageid`, `content1`.`text` AS `title` FROM ( `content` AS `content1` INNER JOIN `pages` ON (`content1`.`contentid`=`pages`.`title`)) WHERE `pages`.`pageid`='1' AND `content1`.`languageid`='1'

This returns:

'1', 'Test Title'

But I can't work out how to expand it to return the rest. Please help!