Professional Web Applications Themes

multi table select - MySQL

[I have been using MySQL with Php for a couple of years and have been doing fine with simple INSERT, UPDATE, SELECT, and DELETE statements. But now I have a need to use multiple table SELECTs and I am having some trouble.] I have two tables - table1 and table2. Their structures are identical and contain a column called status (an integer to store http response status codes - e.g., 200, 403, 404, ....). I would like to get a list of the distinct values of status across both tables. I have tried to use the following query: SELECT DISTINCT ...

  1. #1

    Default multi table select

    [I have been using MySQL with Php for a couple of years and have been
    doing fine with simple INSERT, UPDATE, SELECT, and DELETE statements.
    But now I have a need to use multiple table SELECTs and I am having some
    trouble.]

    I have two tables - table1 and table2. Their structures are identical
    and contain a column called status (an integer to store http response
    status codes - e.g., 200, 403, 404, ....). I would like to get a list
    of the distinct values of status across both tables.

    I have tried to use the following query:
    SELECT DISTINCT status FROM table1, table2 ORDER BY status

    This does not not work. I get:
    Column 'status' in field list is ambiguous

    How should I go about doing this. Note that the list of tables is going
    to grow, so I will ultimately have a table3, table4, ... and so on.

    Also, ..... I'm not sure what happened, but when I tried:
    SELECT table1.status, table2.status FROM table1, table2

    .... MySQL seemed to go into an infinite loop (grabbed 100% CPU - disk
    thrashing - and would not let go). I could not get my Windows XP
    machine to respond without powering it off. I'd like to understand why
    that happened so I can avoid doing something like that again.
    Chuck Anderson Guest

  2. #2

    Default Re: multi table select

    "Chuck Anderson" <websiteaddressseemy.sig> wrote in message
    news:65WdnQU7d-k1nnHeRVn-iwcomcast.com...
    > [I have been using MySQL with Php for a couple of years and have been
    > doing fine with simple INSERT, UPDATE, SELECT, and DELETE statements. But
    > now I have a need to use multiple table SELECTs and I am having some
    > trouble.]
    >
    > I have two tables - table1 and table2. Their structures are identical and
    > contain a column called status (an integer to store http response status
    > codes - e.g., 200, 403, 404, ....). I would like to get a list of the
    > distinct values of status across both tables.
    >
    > I have tried to use the following query:
    > SELECT DISTINCT status FROM table1, table2 ORDER BY status
    The syntax you have used above is called a "join" and it could be thought of
    as combining the two tables "side by side" instead of "one after the other".
    You probably want to use UNION to list the records of one table, followed by
    the records of the other table.

    SELECT DISTINCT table1.status
    UNION
    SELECT DISTINCT table2.status

    UNION returns only the distinct rows from both of its component queries (by
    default).

    Read more about UNION in MySQL here:
    [url]http://dev.mysql.com/doc/refman/5.0/en/union.html[/url]
    > Also, ..... I'm not sure what happened, but when I tried:
    > SELECT table1.status, table2.status FROM table1, table2
    >
    > ... MySQL seemed to go into an infinite loop (grabbed 100% CPU - disk
    > thrashing - and would not let go). I could not get my Windows XP machine
    > to respond without powering it off. I'd like to understand why that
    > happened so I can avoid doing something like that again.
    What you did was called a "Cartesian product". You have matched every row
    in table1 to every row in table2. The result set has N x M rows, where N is
    the number of rows in table1 and M is the number of rows in table2. You can
    see how this can be a very large amount of data, and it is costly to
    generate such a result set.

    Cartesian products have some good uses, but typically when you join two
    tables together like that, you want to specify some condition that defines
    how the rows relate to one another, so that only truly matching rows are
    returned.

    I think it's a bit surprising that you have used MySQL for as long as you
    have, but never learned about joins. Using a relational database without
    understanding joins is like using a language like PHP without understanding
    a "while" loop. It's possible to do some useful work without using theses
    features, but you're sure missing out on a lot of the power you have
    available to you. Joins are fundamental to relational databases.

    You should read one of the SQL tutorials on the internet that discuss joins.
    Just Google for "sql join tutorial" or similar search terms.

    Or read this Wikipedia article on joins in SQL:
    [url]http://en.wikipedia.org/wiki/Join_%28SQL%29[/url]

    Or read reference doentation for joins in MySQL here:
    [url]http://dev.mysql.com/doc/refman/5.0/en/join.html[/url]

    Regards,
    Bill K.


    Bill Karwin Guest

  3. #3

    Default Re: multi table select

    Bill Karwin wrote:
    >"Chuck Anderson" <websiteaddressseemy.sig> wrote in message
    >news:65WdnQU7d-k1nnHeRVn-iwcomcast.com...
    >
    >
    >>[I have been using MySQL with Php for a couple of years and have been
    >>doing fine with simple INSERT, UPDATE, SELECT, and DELETE statements. But
    >>now I have a need to use multiple table SELECTs and I am having some
    >>trouble.]
    >>
    >>I have two tables - table1 and table2. Their structures are identical and
    >>contain a column called status (an integer to store http response status
    >>codes - e.g., 200, 403, 404, ....). I would like to get a list of the
    >>distinct values of status across both tables.
    >>
    >>I have tried to use the following query:
    >>SELECT DISTINCT status FROM table1, table2 ORDER BY status
    >>
    >>
    First off, thanks for the polite and most patient "RTFM " τΏΤ¬ response.
    >The syntax you have used above is called a "join" and it could be thought of
    >as combining the two tables "side by side" instead of "one after the other".
    >You probably want to use UNION to list the records of one table, followed by
    >the records of the other table.
    >
    >SELECT DISTINCT table1.status
    >UNION
    >SELECT DISTINCT table2.status
    >
    >UNION returns only the distinct rows from both of its component queries (by
    >default).
    >
    >Read more about UNION in MySQL here:
    >[url]http://dev.mysql.com/doc/refman/5.0/en/union.html[/url]
    >
    >
    I think I'll start with some tutorials and use the manual once I get a
    basic understanding of what I'm doing.

    But, ... thanks for the very clear example and a "gimme" for this first
    query I got stuck on.

    The application I have written is using an Apache access log file
    yzer that uses database tables and I decided to keep each month's
    entries in its own table (has made my overall implementation easier as I
    have to download my log files through Cpanel which makes things
    complicated at the end of the month - and it allows me to easily ignore
    or simply archive older months as they age.)

    The point is that I will have to understand how to do more queries of
    this kind (UNION/JOIN) in order to write meaningful queries of this data.

    One question: as I add more months of tables (I only have Jan '06 and
    Feb '06 - table1 and table2 - so far) should I just keep compounding
    that UNION syntax you showed me above or will I find a better way?
    >
    >
    >>Also, ..... I'm not sure what happened, but when I tried:
    >>SELECT table1.status, table2.status FROM table1, table2
    >>
    >>... MySQL seemed to go into an infinite loop (grabbed 100% CPU - disk
    >>thrashing - and would not let go). I could not get my Windows XP machine
    >>to respond without powering it off. I'd like to understand why that
    >>happened so I can avoid doing something like that again.
    >>
    >>
    >
    >What you did was called a "Cartesian product". You have matched every row
    >in table1 to every row in table2. The result set has N x M rows, where N is
    >the number of rows in table1 and M is the number of rows in table2. You can
    >see how this can be a very large amount of data, and it is costly to
    >generate such a result set.
    >
    >
    Big oops. So I guess if I had been patient the thrashing would have
    stopped, ... eventually.
    >Cartesian products have some good uses, but typically when you join two
    >tables together like that, you want to specify some condition that defines
    >how the rows relate to one another, so that only truly matching rows are
    >returned.
    >
    >I think it's a bit surprising that you have used MySQL for as long as you
    >have, but never learned about joins. Using a relational database without
    >understanding joins is like using a language like PHP without understanding
    >a "while" loop. It's possible to do some useful work without using theses
    >features, but you're sure missing out on a lot of the power you have
    >available to you. Joins are fundamental to relational databases.
    >
    >
    I was a pretty good C/C++ programmer until I quit using it in the early
    90's, so Php was quite easy to pick up a couple of years ago. I have
    learned to use MySQL through example (and by keeping it simple).

    I upgraded to MySQL 4.1 for this project so that I could do subqueries,
    and I have had some success with that, but I have never been able to,
    nor have I taken the time to understand what JOINS and UNIONS are.

    It's obvious that it is now time for me to invest some time and energy
    to educate myself.
    >You should read one of the SQL tutorials on the internet that discuss joins.
    >Just Google for "sql join tutorial" or similar search terms.
    >
    >Or read this Wikipedia article on joins in SQL:
    >[url]http://en.wikipedia.org/wiki/Join_%28SQL%29[/url]
    >
    >Or read reference doentation for joins in MySQL here:
    >[url]http://dev.mysql.com/doc/refman/5.0/en/join.html[/url]
    >
    >Regards,
    >Bill K.
    >
    >
    >
    Thanks, Bill. You've been very helpful.

    --
    *****************************
    Chuck Anderson • Boulder, CO
    [url]http://www.CycleTourist.com[/url]
    Integrity is obvious.
    The lack of it is common.
    *****************************
    Chuck Anderson Guest

  4. #4

    Default Re: multi table select

    "Chuck Anderson" <websiteaddressseemy.sig> wrote in message
    news:M5SdnXWXXriGs3HenZ2dnUVZ_vudnZ2dcomcast.com. ..
    > First off, thanks for the polite and most patient "RTFM " τΏΤ¬ response.
    Thanks! I firmly believe that being rude never helps people learn.
    I've done teaching/tutoring/training off and on since 1986, and that
    principle has been borne out pretty consistently.
    > I think I'll start with some tutorials and use the manual once I get a
    > basic understanding of what I'm doing.
    That's a good idea. Yep, it's usually hard to learn new topics from
    reference docs.
    Task-oriented and tutorial-style docs are much better for that.
    > One question: as I add more months of tables (I only have Jan '06 and Feb
    > '06 - table1 and table2 - so far) should I just keep compounding that
    > UNION syntax you showed me above or will I find a better way?
    I'd do it by using one table, and making sure there's a field of type DATE.
    Then you can use the awesome power of SQL expressions ;-) to filter by a
    particlar range of dates.

    And make sure to keep regular backups. Databases are delicate things, and
    the touch of a button can make it all go away (e.g. hitting return before
    you've given a WHERE clause to a DELETE statement)!
    > I upgraded to MySQL 4.1 for this project so that I could do subqueries,
    > and I have had some success with that, but I have never been able to, nor
    > have I taken the time to understand what JOINS and UNIONS are.
    Tip: You'll find that many kinds of tasks that you can solve using
    subqueries can also be solved by using joins. See
    [url]http://dev.mysql.com/doc/refman/5.0/en/rewriting-subqueries.html[/url] for some
    examples.

    Regards,
    Bill K.


    Bill Karwin Guest

Similar Threads

  1. Updating table with multi-select listbox
    By MikeyJ in forum Macromedia ColdFusion
    Replies: 0
    Last Post: March 23rd, 07:20 AM
  2. Multi Select List box
    By greekchild in forum Dreamweaver AppDev
    Replies: 5
    Last Post: February 22nd, 08:15 PM
  3. multi-select box validation
    By Greg Bryant in forum PHP Development
    Replies: 1
    Last Post: November 8th, 02:58 AM
  4. select multi-files in one times?
    By SafariTECH in forum Macromedia Director Basics
    Replies: 0
    Last Post: August 7th, 02:37 PM
  5. retrieving multi-select form values
    By Hank Tt in forum PHP Development
    Replies: 2
    Last Post: July 31st, 06:14 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