Web Analytics Made Easy -
StatCounter Query that joins a table with its alias help - CodingForum

Announcement

Collapse
No announcement yet.

Query that joins a table with its alias help

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Query that joins a table with its alias help

    Hi, I have 2 tables: COMPETITION and ADVANCEMENT. ADVANCEMENT pretty much just has 2 colums: idOLDCOMP and idNEWCOMP.

    The tables are part of a contest system and the idea is that I can set a competition to have one of the competitors be the winner of a previous competition. I'm having problems making the join however. This is what I'm trying to do:

    Code:
    SELECT COMPETITION.*, OLDCOMP.* FROM COMPETITION 
    JOIN ADVANCEMENT ON COMPETITION.idCOMP = ADVANCEMENT.idNEWCOMP
    JOIN COMPETITION AS OLDCOMP ON COMPETITION.idCOMP = OLDCOMP.idOLDCOMP WHERE COMPETITION.idCOMP = some_id
    This should work great in my head but I think I'm doing it wrong because it isn't returning the result I expected. It's part of a bigger query so if the problem isn't with this part I'll post the rest in a bit.

    Thanks a lot,

    John
    Search for Laughter or Just Search?
    GiggleSearch.org
    Blog: www.johnbeales.com
    All About Ballet: www.the-ballet.com

  • #2
    This isn't the entire query? What else does it do?

    I see you're joining three tables: competition, advancement, and another instance of competition. In your WHERE clause, you're saying "only get rows that are equal to a specific competition ID." But then in your JOIN clause, you say "only get old competition rows that are equal to that same specific ID." Is it possible to have two rows with the same ID in the competition table? It would have to be in order for any old competition rows to be returned. Perhaps did you mean to join old competition ID with the idOLDCOMP column in the advancement table?

    Comment


    • #3
      Yes, I did mean I would join the competition ID with the idOLDCOMP in the ADVANCEMENT table.

      Here's the corrected query:
      Code:
      SELECT COMPETITION.*, OLDCOMP.* FROM COMPETITION 
      JOIN ADVANCEMENT ON COMPETITION.idCOMP = ADVANCEMENT.idNEWCOMP
      JOIN COMPETITION AS OLDCOMP ON COMPETITION.idCOMP = ADVANCEMENT.idOLDCOMP 
      WHERE COMPETITION.idCOMP = some_id
      This is a video competition so the query also selects some video information if the contestants aren't winners of previous competitions.

      Here's the whole thing:
      Code:
      SELECT COMPETITION.*, VIDEO.*, ADVANCEMENT.*, OLDCOMP.* 
      FROM COMPETITION 
      LEFT JOIN VID_to_COMP ON COMPETITION.idCOMP = VID_to_COMP.idCOMP
      LEFT JOIN VIDEO ON VID_to_COMP.idVIDEO = VIDEO.idVIDEO 
      LEFT JOIN ADVANCEMENT ON COMPETITION.idCOMP = ADVANCEMENT.idNEWCOMP 
      LEFT JOIN COMPETITION AS OLDCOMP ON ADVANCEMENT.idOLDCOMP = OLDCOMP.idCOMP 
      WHERE COMPETITION.idCOMP = some_competition_id 
      ORDER BY COMPETITION.COMP_Name Asc";
      Hope this helps!
      Search for Laughter or Just Search?
      GiggleSearch.org
      Blog: www.johnbeales.com
      All About Ballet: www.the-ballet.com

      Comment


      • #4
        Does the corrected query get you to where you want to be? How close is it now? We need more feedback on what the results are now.

        Comment


        • #5
          Hi,
          No, the corrected query doesn't help. The error was just me typing into the post it's been correct in my script the whole time.

          The results of the whole query, (the second one in my previous post), is that it returns results from the table VIDEO as expected but not from the table COMPETITION.

          The reason I'm using left joins for everything is that each competition may be made up of videos in the VIDEO table or videos that are winners of another competition, (hence the ADVANCEMENT table), or some competition of the two so I am selecting everything and I'll test for NULL values later. Also, if I need a winner of another competition, (via the ADVANCEMENT table), I'll actually figure out and select the winner with a different query. For this query I just need the previous competition and any videos that are not previous winners.
          Search for Laughter or Just Search?
          GiggleSearch.org
          Blog: www.johnbeales.com
          All About Ballet: www.the-ballet.com

          Comment


          • #6
            One more thing - I don't think it matters for this query but I forgot to mention before I'm currently running MySQL 4.0.23 (or something). It's pre-4.1, (no subqueries and old Timestamp formatting). I will want to be able to run on both old and new systems thoguh.
            Search for Laughter or Just Search?
            GiggleSearch.org
            Blog: www.johnbeales.com
            All About Ballet: www.the-ballet.com

            Comment


            • #7
              can you show some sample rows and your expected output from those rows? it might be clearer to see what your query needs to do that way.

              Comment


              • #8
                Hi,

                I've made some progress! It turns out that the query is working properly, at least when I call it from MyDB Studio. The problem appears to be with bringing the data into PHP.

                I think that PHP is choking because I have aliased a table. When I use mysql_fetch_assoc() to get my row of data it only gives me one instance of each column from the aliased table, (there should be two: one for the non-aliased row and one for the aliased row), and assigns the values of the second, (aliased), instance of that table to those columns, (this is often null so it appears I'm not getting the info I need).

                So now, how do I use mysql_fetch_assoc() or some other similar mysql/php function to bring a row from my result set into PHP? (if you feel that I this should now be moved into the PHP forum, or I should mark this resolved and start a new thread over there just say so).
                Search for Laughter or Just Search?
                GiggleSearch.org
                Blog: www.johnbeales.com
                All About Ballet: www.the-ballet.com

                Comment


                • #9
                  i don't realy understand a lott of your explanation (aliased tables? where? aliased rows? what's that? "assigns the values of the second, (aliased), instance of that table to those columns" --> what does that mean?)

                  your problem is obviously the incorrect use of the * in the fieldlist.
                  you should never use * in embedded sql, because it will create to large recordsets + it will cause the problems you're now having when you have the same columnnames in more then 1 table from your tablelist.
                  so you just need to replace the
                  COMPETITION.*, VIDEO.*, ADVANCEMENT.*, OLDCOMP.*
                  into
                  COMPETITION.field1, COMPETITION.field2, COMPETITION.field3, VIDEO.anotherfield1, VIDEO.anotherfield2, VIDEO.field3 AS video_field3, ADVANCEMENT.yetanotherfield1, ADVANCEMENT.field2 AS whatever_field2, ADVANCEMENT.yetanotherfield3, OLDCOMP.foobar
                  so you just add the actual columns to the fieldlist and where you have duplicate columnnames, you use an alias (like in the " AS video_field3").
                  In PHP, you then refer to the alias, like
                  PHP Code:
                  while ($row mysql_fetch_assoc($result)){
                     echo 
                  $row['video_field3'];

                  with your current query, the resultset will initially contain the 2 or more columns with the same columnname, but when an associative array is created, the value of the second column will overwrite the value of the previous column with the identical name. and the value of a possible third column with the same name would overwrite it as well. because no new element is added to the associated array sine the key already exists --> just assigns the value from the second column to the already existing key.

                  if you would use mysql_fetch_row() and then refer to the values like
                  PHP Code:
                  while ($row mysql_fetch_row($result)){
                     echo 
                  $row[4];

                  then you wouldn't need to change your query, because it will add a new element for each column (starting with 0 for the first column), regardless of it's name. But using the enumerated array is not as transparant as using an associated array...
                  Posting guidelines I use to see if I will spend time to answer your question : http://www.catb.org/~esr/faqs/smart-questions.html

                  Comment


                  • #10
                    Sorry about my poor explanation. You described what is happening exactly right here:
                    Originally posted by raf View Post
                    with your current query, the resultset will initially contain the 2 or more columns with the same columnname, but when an associative array is created, the value of the second column will overwrite the value of the previous column with the identical name. and the value of a possible third column with the same name would overwrite it as well. because no new element is added to the associated array sine the key already exists --> just assigns the value from the second column to the already existing key.
                    I didn't want to have to write out each column name, (and aliases for duplicate columns), that's why I was using the *. I did find an interesting function though on the PHP manual site that I modified like this:
                    PHP Code:
                    <?php
                    $sql 
                    "SELECT a.*, b.* from table1 a, table2 b WHERE a.id=b.id"// example sql
                    $r mysql_query($sql,$conn);
                    if (!
                    $r) die(mysql_error());
                    $numfields mysql_num_fields($r);
                    $tfields = Array();
                    for (
                    $i=0;$i<$numfields;$i++)
                    {
                       
                    $field =  mysql_fetch_field($r,$i);
                       
                    $tfields[$i] = $field->table.'.'.$field->name;
                    }
                    $assocArray = Array();
                    while (
                    $row mysql_fetch_row($r))
                    {
                       
                    $rowAssoc = Array();
                       for (
                    $i=0;$i<$numfields;$i++)
                       {
                           
                    $rowAssoc[$tfields[$i]] = $row[$i];
                       }
                    $assocArray[count($assocArray)] = $rowAssoc;
                    }
                    ?>
                    This leaves you with a 2 dimensional associative array that references each database column using the TABLENAME.COLUMN_NAME scheme. Very handy. You can't quite treat it as a result set, (using the while loop), but a foreach construct will work fine.

                    So, I suppose there are a couple of ways to deal with any problem. Honestly I probably should have just written out the table names with aliases. I am glad however to have found this function as I think it'll be handy in the future.
                    Search for Laughter or Just Search?
                    GiggleSearch.org
                    Blog: www.johnbeales.com
                    All About Ballet: www.the-ballet.com

                    Comment


                    • #11
                      get out of the bad habit of using SELECT * it may save you some typing time but will wreck havoc with your results and your queries as you have already seen.

                      name the columns you need.

                      Comment


                      • #12
                        I'm already starting! Thanks a lot.
                        Search for Laughter or Just Search?
                        GiggleSearch.org
                        Blog: www.johnbeales.com
                        All About Ballet: www.the-ballet.com

                        Comment


                        • #13
                          Originally posted by johnnyb View Post
                          So, I suppose there are a couple of ways to deal with any problem.
                          Oh boy. How many times i've got that reply when a better sollution was proposed to someone who actually didn't have a clue what he was doing and/or why.
                          Originally posted by johnnyb View Post
                          Honestly I probably should have just written out the table names with aliases. I am glad however to have found this function as I think it'll be handy in the future.
                          Yeah. In all the years is developped db-driven webapplications, i never needed something like it and it doesn't realy looks efficient to me either. but i imagine you live in another kind of world then me...
                          Posting guidelines I use to see if I will spend time to answer your question : http://www.catb.org/~esr/faqs/smart-questions.html

                          Comment

                          Working...
                          X