Professional Web Applications Themes

Select, Join & field values - 2 tables - MySQL

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 ...

  1. #1

    Default Select, Join & field values - 2 tables

    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]justinkoivi.com[/email]
    [url]http://koivi.com[/url]
    Justin Koivisto Guest

  2. #2

    Default Re: Select, Join & field values - 2 tables

    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]jstucklexattglobal.net[/email]
    ==================
    Jerry Stuckle Guest

  3. #3

    Default Re: Select, Join & field values - 2 tables

    "Justin Koivisto" <justinkoivi.com> wrote in message
    news:ebKdnaneZtx1BG3enZ2dnUVZ_sGdnZ2donvoy.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.


    Bill Karwin Guest

  4. #4

    Default Re: Select, Join & field values - 2 tables

    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]justinkoivi.com[/email]
    [url]http://koivi.com[/url]
    Justin Koivisto Guest

Similar Threads

  1. Listing join from tables...
    By createmedia in forum Coldfusion Database Access
    Replies: 5
    Last Post: June 5th, 09:02 PM
  2. New to Joines - Inner Join on 4 Tables
    By FusionRed in forum Coldfusion - Getting Started
    Replies: 2
    Last Post: June 14th, 03:14 PM
  3. HELP.. Recordset JOIN tables
    By Addy_31 in forum Dreamweaver AppDev
    Replies: 1
    Last Post: May 4th, 01:51 AM
  4. trying to update a table after making a join select query on two tables
    By rob merritt in forum ASP.NET Data Grid Control
    Replies: 0
    Last Post: March 1st, 10:33 PM
  5. SELECT from multiple tables (not join though)
    By Madison Kelly in forum PostgreSQL / PGSQL
    Replies: 15
    Last Post: January 10th, 07:43 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139