Select, Join & field values - 2 tables

[Justin Koivisto]

I had originally posted this in alt.comp.databases.mysql with no replies
(I thought I had posted it here, my bad). Hopefully one of you bright
individuals could shed some light on this for me:

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?

BTW - mysql 3.23.58 is one of the targeted systems, but I am also
interested in answers for newer versions as well.

Thanks

--
Justin Koivisto, ZCE - [email]justin@koivi.com[/email]
[url]http://koivi.com[/url]

[Jerry Stuckle]

Justin Koivisto wrote:

> I had originally posted this in alt.comp.databases.mysql with no replies
> (I thought I had posted it here, my bad). Hopefully one of you bright
> individuals could shed some light on this for me:
>
> 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?
>
> BTW - mysql 3.23.58 is one of the targeted systems, but I am also
> interested in answers for newer versions as well.
>
> Thanks
>

Justin,

Well, it's doing exactly like you said.

You specify in your inner join that content.page_title must start with
'PREFIX_'. Your where clause then specifies you display those rows
which have content.page_title starting with 'PREFIX_'.

IOW, your INNER JOIN and your WHERE clause both specify the same rows.

But I do have a problem with this and performance. You're asking MySQL
to contactenate 'PREFIX_' to EVERY ROW in content.page title before
making a match. This will be a huge amount of overhead if you have a
very large table. And MySQL probably won't be able to use indexes on
it, so you'll do a full table scan.

I think you'll be better separating the PREFIX_ and the rest of the
content.page_title column into two different columns when you store the
data in the table. That way MySQL will be able to use indexes if
appropriate and not have to concatenate every row.

If this doesn't answer your question, can you give us a little more
information, like what is in those columns and just what you're expecting?

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
[email]jstucklex@attglobal.net[/email]
==================

[Bill Karwin]

"Justin Koivisto" <justin@koivi.com> wrote in message
news:ebKdnaneZtx1BG3enZ2dnUVZ_sGdnZ2d@onvoy.com...

> For instance, I want to get the following:
>
> '2006-01-01 08:32:17', 'admin3', 'index.html'
>
> 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_%'

The WHERE clause in this query is redundant. The value in
content.page_title must begin with 'PREFIX_' if it matches another string
which has 'PREFIX_' prepended.

Other than that, that query should return what you described. Am I missing
something?

Regards,
Bill K.

[Justin Koivisto]

Jerry Stuckle wrote:

> Justin Koivisto wrote:

>> I had originally posted this in alt.comp.databases.mysql with no replies
>> (I thought I had posted it here, my bad). Hopefully one of you bright
>> individuals could shed some light on this for me:
>>
>> 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?
>>
>> BTW - mysql 3.23.58 is one of the targeted systems, but I am also
>> interested in answers for newer versions as well.

>
> Well, it's doing exactly like you said.
>
> You specify in your inner join that content.page_title must start with
> 'PREFIX_'. Your where clause then specifies you display those rows
> which have content.page_title starting with 'PREFIX_'.
>
> IOW, your INNER JOIN and your WHERE clause both specify the same rows.

some days (or months) I just can't function... ;)

> But I do have a problem with this and performance. You're asking MySQL
> to contactenate 'PREFIX_' to EVERY ROW in content.page title before
> making a match. This will be a huge amount of overhead if you have a
> very large table. And MySQL probably won't be able to use indexes on
> it, so you'll do a full table scan.

gee - without using the indexes, it's totally useless then.

> I think you'll be better separating the PREFIX_ and the rest of the
> content.page_title column into two different columns when you store the
> data in the table. That way MySQL will be able to use indexes if
> appropriate and not have to concatenate every row.

I think I will just leave it the way I have it now using loops and
additional queries. That way I am not running through the entire table
for the searches.

> If this doesn't answer your question, can you give us a little more
> information, like what is in those columns and just what you're expecting?

In this instance, I can't change the data structure, but on the next
version of the system I have free reign, and will have *everything*
quite a bit different.

Thanks for the answer!

--
Justin Koivisto, ZCE - [email]justin@koivi.com[/email]
[url]http://koivi.com[/url]