select, joins & field values in 2 tables

Firstly, here's the example structure and data:

+---------------+
| log |
+---------------+
| id |
| approval_date |
| approved_by |
| page_title |
+---------------+

Data:
4, '2006-01-01 08:32:17', 'admin3', 'index'
....

+---------------+
| content |
+---------------+
| page_title |
| page_uri |
+---------------+

Data:
'PREFIX_index', 'index.html'
'index', 'index.html'
'ARCH_index', 'index.html'


What I want to do is select log.approval_date, log.approved_by and
content.page_uri for the records... The twist is that I want to join the
tables based on the page_title where they will be *different* values in
each table.

For instance, I want to get the following:

'2006-01-01 08:32:17', 'admin3', 'index.html'

This would require that the following would be the constraint for the JOIN:
CONCAT('PREFIX_', log.page_title) = content.page_title

This is the first thing I tried:
SELECT
content.page_uri
, log.approval_date
, log.approved_by
, REPLACE(content.page_title,'PREFIX_','') as xx
FROM content
INNER JOIN log
ON log.page_title = xx
WHERE
content.page_title LIKE 'PREFIX_%'

*** MySQL said: #1054 - Unknown column 'xx' in 'on clause'

Then I tried this:
SELECT
content.page_uri
, log.approval_date
, log.approved_by
FROM content
INNER JOIN log
ON CONCAT('PREFIX_', log.page_title ) = content.page_title
WHERE
content.page_title LIKE 'PREFIX_%'

.... this returned data, but looks like it's pretty much everything in
the log table coupled with the matching entries from the content table
(from the WHERE clause).

Can anyone get me in the right direction for this?

--
Justin Koivisto, ZCE - justin@koivi.com
http://koivi.com

Justin Koivisto wrote:
> Firstly, here's the example structure and data:
>
> +---------------+
> | log |
> +---------------+
> | id |
> | approval_date |
> | approved_by |
> | page_title |
> +---------------+
>
> Data:
> 4, '2006-01-01 08:32:17', 'admin3', 'index'
> ...
>
> +---------------+
> | content |
> +---------------+
> | page_title |
> | page_uri |
> +---------------+
>
> Data:
> 'PREFIX_index', 'index.html'
> 'index', 'index.html'
> 'ARCH_index', 'index.html'
>
>
> What I want to do is select log.approval_date, log.approved_by and
> content.page_uri for the records... The twist is that I want to join the
> tables based on the page_title where they will be *different* values in
> each table.
>
> For instance, I want to get the following:
>
> '2006-01-01 08:32:17', 'admin3', 'index.html'
>
> This would require that the following would be the constraint for the JOIN:
> CONCAT('PREFIX_', log.page_title) = content.page_title
>
> This is the first thing I tried:
> SELECT
> content.page_uri
> , log.approval_date
> , log.approved_by
> , REPLACE(content.page_title,'PREFIX_','') as xx
> FROM content
> INNER JOIN log
> ON log.page_title = xx
> WHERE
> content.page_title LIKE 'PREFIX_%'
>
> *** MySQL said: #1054 - Unknown column 'xx' in 'on clause'
>
> Then I tried this:
> SELECT
> content.page_uri
> , log.approval_date
> , log.approved_by
> FROM content
> INNER JOIN log
> ON CONCAT('PREFIX_', log.page_title ) = content.page_title
> WHERE
> content.page_title LIKE 'PREFIX_%'
>
> ... this returned data, but looks like it's pretty much everything in
> the log table coupled with the matching entries from the content table
> (from the WHERE clause).
>
> Can anyone get me in the right direction for this?
>

By the lack of replies, I will just assume that this is not possible and
abandon the thought.

--
Justin Koivisto, ZCE - justin@koivi.com
http://koivi.com