It's generally a bad idea to store delimited data in a single column in a database. It kind of goes against what a database is there for. A better layout would be to have your second table contain a row for each of the "delimited" values.
So if you have '1,2,3,4' in your database now, the better way would be to have four rows, 1 through 4 in the table instead, with whatever other info you keep for each row. Then a simple SELECT var, COUNT(var) FROM table will give you the answers you're looking for. but...if you want to leave it the way it is, then you can use some kind of matching to find the rows using LIKE. I couldn't get a query to work, though. If your data can be seperated by commas, an easy way to do it is with something like this SELECT table1.key, COUNT(*) FROM table1, table2 WHERE FIND_IN_SET(table1.key,table2.keys) GROUP BY table1.key But, the flaw of that is that it'll only count one occurance of table1.key per row in table2. If a row has 'blue' twice, only one will be counted. ---John Holmes... ----- Original Message ----- From: "ROBERT MCPEAK" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Thursday, May 23, 2002 1:22 PM Subject: [PHP] php/mysql join query help > I have a table with a field "key" containing a unique value. I have a > second table containing a field called "keys" containg a pipe-delimited > list of values from the "key" table. > > I'd like to do a query that took each value from key, and matched it > agains the field "keys" in the second table, and returned a third table > of unique key values from the first table, and number of matches from > the second table. > > I've already set up a full text index for the values in the second > table and have done successful matches in simpler queries. > > Make sense? > > The table I'd like to generate might look like this: > > key matches > blue 50 > yellow 6 > green 29 > > This would indicate that there were 3 key values in the first table > (blue, yellow, green), and, respectively, there were 50, 6, and 20 > matches for each term in the second table. > > Can somebody help me do this? > > Thanks! > > -Bob > > -- > PHP General Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
