SitePoint Sponsor

User Tag List

Results 1 to 3 of 3

Thread: Help with Joins

  1. #1
    SitePoint Zealot m3avrck's Avatar
    Join Date
    Sep 2002
    Location
    Arlington, VA
    Posts
    122
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Help with Joins

    Hi everyone. I'm new to mySQL but I understand the basics.

    I'm just wondering how I would go about the following:

    Lookup value from table1 and then retrieve all values in table2 that have the ID from the table1 value that is selected

    I think I need to join like the following:

    $query= 'SELECT `errorname` FROM `level0` LEFT JOIN `level1` ON `level0ID`=`level1ID`';

    But for some reason I'm getting "Column: 'errorname' in field list is ambiguous" ...any ideas? Here are what my tables look like:


    Code:
    +----------+-----------+-----------+------------+-----------+
    | level0ID | tibetName | errorName | tibetReady | validated |
    +----------+-----------+-----------+------------+-----------+
    |        4 | AA        | AA        |          0 |         0 |
    |        5 | BB        | BB        |          0 |         0 |
    |        6 | CC        | CC        |          0 |         0 |
    |        7 | DD        | DD        |          0 |         0 |
    +----------+-----------+-----------+------------+-----------+
    
    +----------+-----------+-----------+------------+-----------+----------+
    | level1ID | tibetName | errorName | tibetReady | validated | level0ID |
    +----------+-----------+-----------+------------+-----------+----------+
    |        5 | aa        | aa        |       NULL |      NULL |        4 |
    |        7 | cc        | cc        |       NULL |      NULL |        6 |
    |        6 | bb        | bb        |       NULL |      NULL |        5 |
    |        8 | dd        | dd        |       NULL |      NULL |        7 |
    |        9 | no        | no        |       NULL |      NULL |        0 |
    +----------+-----------+-----------+------------+-----------+----------+
    Basically all I want to do, is if the user selected level0ID=4, I want to show all level1's who have level0ID=4.

    Thanks.

  2. #2
    SitePoint Zealot m3avrck's Avatar
    Join Date
    Sep 2002
    Location
    Arlington, VA
    Posts
    122
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I did manage to get this to work:

    PHP Code:
    $query"SELECT `level0ID` FROM `level0` WHERE `errorName`='$s'";
      
    $resultdbQuery($query);
      
    $nummysql_fetch_row($result);
       
      
    $query"SELECT `errorName` FROM `level1` WHERE `level0ID`='$num[0]'";
      
    $resultdbQuery($query); 

    But I am assuming I could have done this with a JOIN or something. But I can't get this to work at all. Maybe my database is messed up using the same names in each table. Any ideas?

  3. #3
    Web-coding NINJA! silver trophy beetle's Avatar
    Join Date
    Jul 2002
    Location
    Dallas, TX
    Posts
    2,900
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    SELECT l1.errorName 
    FROM level0 l0
    level1 l1
    WHERE l1
    .level0ID l0.level0ID
    GROUP BY l1
    .errorName 
    beetle a.k.a. Peter Bailey
    blogs: php | prophp | security | design | zen | software
    refs: dhtml | gecko | prototype | phpdocs | unicode | charsets
    tools: ide | ftp | regex | ffdev





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
  •