Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 13 of 13
  1. #1
    Regular Coder
    Join Date
    Apr 2003
    Location
    Montreal, QC
    Posts
    340
    Thanks
    3
    Thanked 2 Times in 2 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
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    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?

  • #3
    Regular Coder
    Join Date
    Apr 2003
    Location
    Montreal, QC
    Posts
    340
    Thanks
    3
    Thanked 2 Times in 2 Posts
    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

  • #4
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    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.

  • #5
    Regular Coder
    Join Date
    Apr 2003
    Location
    Montreal, QC
    Posts
    340
    Thanks
    3
    Thanked 2 Times in 2 Posts
    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

  • #6
    Regular Coder
    Join Date
    Apr 2003
    Location
    Montreal, QC
    Posts
    340
    Thanks
    3
    Thanked 2 Times in 2 Posts
    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

  • #7
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,633
    Thanks
    4
    Thanked 148 Times in 139 Posts
    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.

  • #8
    Regular Coder
    Join Date
    Apr 2003
    Location
    Montreal, QC
    Posts
    340
    Thanks
    3
    Thanked 2 Times in 2 Posts
    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

  • #9
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

  • #10
    Regular Coder
    Join Date
    Apr 2003
    Location
    Montreal, QC
    Posts
    340
    Thanks
    3
    Thanked 2 Times in 2 Posts
    Sorry about my poor explanation. You described what is happening exactly right here:
    Quote 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

  • #11
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,633
    Thanks
    4
    Thanked 148 Times in 139 Posts
    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.

  • #12
    Regular Coder
    Join Date
    Apr 2003
    Location
    Montreal, QC
    Posts
    340
    Thanks
    3
    Thanked 2 Times in 2 Posts
    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

  • #13
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote 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.
    Quote 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


  •  

    Posting Permissions

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