SitePoint Sponsor

User Tag List

Results 1 to 15 of 15
  1. #1
    Non-Member
    Join Date
    Jan 2004
    Location
    Seattle
    Posts
    4,328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Database Table Query Join Questions

    This is a little difficult to explain, so bear with me. Below is a query I'm using to exctract data from a table filled with data about people in various nations (cia_ppl)...

    PHP Code:
    $res mysql_query ("SELECT * FROM cia_ppl CP
    LEFT JOIN gw_geog GG on GG.IDArea = CP.ID_Name
    WHERE CP.Pop != 0"
    ) or die (mysql_error()); 
    The WHERE clause makes sure it doesn't display places where no people live (POPulation = 0). I joined it to table gw_geog because that's where the full name of each nation is stored.

    Now I want to join another table that contains my footnotes, and I'm getting confused. First, let me offer some sample rows from the main table, cia_ppl:

    NAME / POP(ulation) / Age_Ave
    aus / 48 million / 25
    can / 60 million / 26
    usa / 300 million / 28
    The footnotes table - cia_notes - matches cia_ppl via the field ID_Name. It contains three other fields for notes, note codes and category, like this:

    ID_Name / ID_Note / Cat(egory) / Note

    aus / 4Pop / Ppl / Note: Australia's population...
    aus / 4PopG / Ppl / Note: Population growth...
    can / 5Econ / Econ / Note: Canada's economy...
    usa / 4Pop / Ppl / Note: The United State's population...
    usa / 4PopG / Ppl / Note: Population growth in the U.S....
    usa / 5Econ / Econ / Note: The U.S. economy is...
    I would like to display the appropriate footnote codes (cia_notes.Cat = Ppl) in a column on the right side of my display table, like this...

    aus / 48 million / 25 / PopPopG
    can / 60 million / 26 /
    usa / 300 million / 28 / PopPopG
    Notice that only footnotes from the PEOPLE category - cia_notes.Cat = Ppl - are included. Later, I'm going to use str_replace to change the footnote codes to superscript numbers. So if Pop(ulation) is represented by the footnote 1 and Population Growth by 3, my table might look like this:

    aus / 48 million / 25 / 1,3
    can / 60 million / 26 /
    usa / 300 million / 28 / 1,3
    So the first thing I need to do is join the table cia_notes in a way that...

    1) Selects data only from rows where Cat = Ppl
    2) But doesn't limit displays to countries that are represented by Ppl notes. In other words, Canada's population, etc. will still be display, even though it has no people footnotes.
    3) I also need to display, where appropriate, multiple note codes (or superscript numbers) in each cell, rather than just one.

    I added a second join, as follows:

    PHP Code:
    $res mysql_query ("SELECT * FROM cia_ppl CP
    LEFT JOIN gw_geog GG on GG.IDArea = CP.ID_Name
    LEFT JOIN cia_notes CN on CN.IDName = CP.ID_Name
    WHERE CP.Pop != 0"
    ) or die (mysql_error()); 
    It basically works, except that it only displays the FIRST footnote, yielding this...

    aus / 48 million / 25 / Pop
    can / 60 million / 26 /
    usa / 300 million / 28 / Pop
    instead of this:

    aus / 48 million / 25 / PopPopG
    can / 60 million / 26 /
    usa / 300 million / 28 / PopPopG
    So, if anyone can see where I'm headed, can you show me how to modify my query or PHP display, so that all relevant footnotes are displayed? And can you also show me how to limit the footnotes my script searches to PEOPLE footnotes (Cat = 'Ppl'), without zapping countries that have no people footnotes at all?

    Thanks.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,248
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    LEFT JOIN cia_notes CN on CN.IDName = CP.ID_Name and CN.Cat = 'Ppl'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    Non-Member
    Join Date
    Jan 2004
    Location
    Seattle
    Posts
    4,328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    LEFT JOIN cia_notes CN on CN.IDName = CP.ID_Name and CN.Cat = 'Ppl'
    Thanks. I was trying to add CN.Cat = Ppl to the WHERE clause in my earlier experiments. On to step two...

    With your fix, the table now displays correctly - except that it displays multiple rows for every nation that has two or more footnotes. I want to display just one row per nation, grouping all its footnotes in one table cell.

    So I added a GROUP BY clause...

    PHP Code:
    "SELECT * FROM cia_ppl CP
    LEFT JOIN gw_geog GG ON GG.IDArea = CP.ID_Name
    LEFT JOIN cia_notes CN ON CN.ID_Name = CP.ID_Name AND CN.Cat = 'Ppl'
    WHERE CP.Pop !=0
    GROUP BY GG.Name 
    Now the table once again displays just one row per nation - but only the first footnote appears in the last column. Does anyone know how to make all the values for cia_notes.ID_Note (where CN.Cat = 'Ppl') display in a single cell, preferably with multiple values separated by commas?

    Thanks.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,248
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    use the GROUP_CONCAT function
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    Non-Member
    Join Date
    Jan 2004
    Location
    Seattle
    Posts
    4,328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    use the GROUP_CONCAT function
    Thanks. It looks like that only works on MySQL 5, right? I'll have to wait until I get my Mac squared away and work on that.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,248
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    GROUP_CONCAT was added in 4.1
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    Non-Member
    Join Date
    Jan 2004
    Location
    Seattle
    Posts
    4,328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    GROUP_CONCAT was added in 4.1
    Oops, I guess I misread something when I visited mysql.com. I'll have another look at it. Thanks again for all the tips.

  8. #8
    Non-Member
    Join Date
    Jan 2004
    Location
    Seattle
    Posts
    4,328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I removed the * from my query and typed in the name of each field I'm querying so I could insert a GROUP_CONCAT function. When I insert this...

    PHP Code:
    GROUP_CONCAT(CN.ID_Note SEPARATOR ","
    ...none of my footnotes display at all. When I change it back to CN.ID_Note, they display as before, with only the first footnote in each series displaying.

    I tried the GROUP_CONCAT function again, this time deleting...

    PHP Code:
    GROUP BY GG.Name 
    This time I got the following error message:

    Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause
    Do I also need to modify the script that precedes my query?:

    PHP Code:
    $result mysql_query('select count(*) from cia_ppl');
    if ((
    $result) && (mysql)result ($result 0) > 0)) {
    } else {
    die(
    'Invalid query: ' mysql_error());
    }

    Thanks.

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,248
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    might help if you showed the actual query that got the actual error

    GROUP_CONCAT does work, i've used it lotsa times
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    Non-Member
    Join Date
    Jan 2004
    Location
    Seattle
    Posts
    4,328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    might help if you showed the actual query that got the actual error

    GROUP_CONCAT does work, i've used it lotsa times
    PHP Code:
    $res mysql_query ("SELECT CP.ID, CP.ID_Name, CP.Pop, CP.Pop2, CP.AgeMed, CP.Age0, CP.Age15, CP.Age65,
    CP.Sex, CP.LifeExp, CP.PopGrowth, CP.LitTotal,
    CN.ID_Name, GROUP_CONCAT(CN.ID_Note SEPARATOR "
    ,"), CN.Cat, GG.IDArea, GG.Name
    FROM cia_ppl CP
    LEFT JOIN cia_notes CN ON CN.ID_Name = CP.ID_Name AND CN.Cat = "
    Ppl"
    WHERE CP.Pop != 0 and CP.ID_Name != 'ear' AND CP.ID_Name != 'eru'
    GROUP BY GG.Name
    ORDER BY CP.ID"
    ) or die (mysql_error()); 

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,248
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    you forgot the error message, but i'm going to guess that it's because you are referring to table GG which doesn't exist!!

    try this --
    Code:
    SELECT CP.ID
         , CP.ID_Name
         , CP.Pop
         , CP.Pop2
         , CP.AgeMed
         , CP.Age0
         , CP.Age15
         , CP.Age65
         , CP.Sex
         , CP.LifeExp
         , CP.PopGrowth
         , CP.LitTotal
         , DT.ppl_notes
      FROM cia_ppl CP 
    LEFT OUTER
      JOIN (
           select ID_Name
                , GROUP_CONCAT(ID_Note) as ppl_notes
             from cia_notes  
            where Cat = "Ppl" 
           group
               by ID_Name
           ) as DT
        ON DT.ID_Name = CP.ID_Name 
     WHERE CP.Pop != 0 
       and CP.ID_Name != 'ear' 
       AND CP.ID_Name != 'eru' 
    ORDER 
        BY CP.ID
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    Non-Member
    Join Date
    Jan 2004
    Location
    Seattle
    Posts
    4,328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    you forgot the error message, but i'm going to guess that it's because you are referring to table GG which doesn't exist!!
    Yikes, I missed my first join:

    PHP Code:
    LEFT JOIN gw_geog GG on GG.IDArea CP.ID_Name 
    I had to type it in rather than copy and paste because I can currently work with PHP and MySQL only on my PC, but I'm connected to the Internet with my Mac. Oh well.

    I'll have a look at your script and try and factor in my missing join. Thanks!

  13. #13
    Non-Member
    Join Date
    Jan 2004
    Location
    Seattle
    Posts
    4,328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well, my fix isn't working, but I could have made any number of mistakes. I inserted my additions in blue text. I temporarily deleted my WHERE clause (green text) because it's giving me an "unexpected T_STRING error. Thanks.

    Code:
    SELECT CP.ID
         , CP.ID_Name
         , CP.Pop
         , CP.Pop2
         , CP.AgeMed
         , CP.Age0
         , CP.Age15
         , CP.Age65
         , CP.Sex
         , CP.LifeExp
         , CP.PopGrowth
         , CP.LitTotal
         , GG.IDArea, GG.Name
         , DT.ppl_notes
      FROM cia_ppl CP 
    LEFT JOIN gw_geog GG ON GG.IDArea = CP.ID_Name
    LEFT OUTER  JOIN (
           select ID_Name
                , GROUP_CONCAT(ID_Note) as ppl_notes
             from cia_notes  
            where Cat = "Ppl" 
           group
               by ID_Name
           ) as DT
        ON DT.ID_Name = CP.ID_Name 
     WHERE CP.Pop != 0 
       and CP.ID_Name != 'ear' 
       AND CP.ID_Name != 'eru' 
    GROUP BY GG.Name
    ORDER BY CP.ID

  14. #14
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,248
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    what is the purpose of the GROUP BY? you aren;t counting or summing anything
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  15. #15
    Non-Member
    Join Date
    Jan 2004
    Location
    Seattle
    Posts
    4,328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    what is the purpose of the GROUP BY? you aren;t counting or summing anything
    OK, I removed the GROUP BY clause, with no change in the display. Then I realized I was trying to display ($row["ID_Note"]), rather than ($row["ppl_notes"]). It works beautifully now.

    Thanks so much.


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
  •