Database Table Query Join Questions

[geosite]

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)...

$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:

$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.

[r937]

LEFT JOIN cia_notes CN on CN.IDName = CP.ID_Name and CN.Cat = 'Ppl'

[geosite]

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...

"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.

[r937]

use the GROUP_CONCAT function

[geosite]

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.

[r937]

GROUP_CONCAT was added in 4.1

[geosite]

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.

[geosite]

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...

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...

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?:

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

Thanks.

[r937]

might help if you showed the actual query that got the actual error

GROUP_CONCAT does work, i've used it lotsa times

[geosite]

might help if you showed the actual query that got the actual error

GROUP_CONCAT does work, i've used it lotsa times

$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()); 

[r937]

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 --

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

[geosite]

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:

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!

[geosite]

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.

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

[r937]

what is the purpose of the GROUP BY? you aren;t counting or summing anything

[geosite]

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.