selecting a count from another table

[aunquarra]

Basically, I have two tables like this:

[code]
table name: keywords
+----+---------+
| id | keyword |
+----+---------+
|  1 | brown  |
|  2 | black  |
+----+---------+

table name: items_keywords
+----+---------+------------+
| id | item_id | keyword_id |
+----+---------+------------+
|  1 |      1 |          1 |
|  2 |      2 |          1 |
|  3 |      1 |          1 |
|  4 |      3 |          1 |
+----+---------+------------+
[/code]

I'm prepared to do the work with PHP, but I wanted to see if mySQL could do this more efficiently. Basically, I want to do some kind of "SELECT * from keywords" that would also include a count(*) from items_keywords where keywords.id=items_keywords.keyword_id.

I'm just starting to learn my way around joins, and I've found in some applications, it's more efficient to just let mySQL do the work rather than adding other table values to PHP arrays. Just want to see if this is possible in this scenario.

[fenway]

Try the following (untested):

[code]SELECT k.keyword, COUNT(*) from keywords as k left join items_keywords as ik on k.id=ik.keyword_id group by ik.keyword_id[/code]

[aunquarra]

That worked! Seems to be pretty clean with demo data. We still have to see how it scales, but looks good so far!

Thanks!

[fenway]

Scaling shouldn't be an issue, provided the necessary columns are indexed.

[aunquarra]

Okay, I figured I'd update this post rather than posting a new topic since this is kinda related.

I tried to use your query as a guide for doing a lookup for a specific item, but it's not quite working the way I saw it in my head.

I'm looking to return something like this (using data from above table):
[code]
Query: SELECT ??????  ik.item_id=3  ?????
+----+---------+----------+
| id | keyword | selected |
+----+---------+----------+
|  1 | brown  |        1 |
|  2 | black  |        0 |
+----+---------+----------+
[/code]

Of course, if there's artifact data in the return (like a column with the item_id in each row), that's fine. I can deal with that. I just need to make sure these three columns are there.

So far, here's what I have. The problem is that it only returns keywords that match the item_id. If the keyword_id isn't in the items_keywords table next to the item_id, it doesn't show up.
[code]
SELECT k.keyword, k.desc, COUNT(*) as selected from keywords as k left join items_keywords as ik on k.id=ik.keyword_id where ik.item_id='3' group by ik.keyword_id order by keyword[/code]

Again, I had originally planned to do the legwork in PHP, but with mySQL, I could sort based on the selected column.

[fenway]

Well, if there isn't a matching keyword_id in the items_keywords table, then yes, a query on ik.item_id='3' will fail.  I'm not sure what you'd like it to return.

[aunquarra]

Well, I'm really trying to get a number of matches. If there aren't any, I'd like it to still show up, but with '0' as the result.

[fenway]

Well, you could move that where condition to the ON clause, which would have it evaluated _before_ the column values got NULLed out.

[aunquarra]

Hmmm... Then it counts the NULL values together as an ik.item_id. So instead of one row for each k.keyword with a 0 as the count, I get one row with the first k.keyword value, and a count of all the other NULLs.

[fenway]

Then I'll have to take another look...

[aunquarra]

Right now, here's how my query is set:

[code]
SELECT k.keyword, k.desc, COUNT(*) AS assigned FROM keywords AS k LEFT JOIN
items_keywords AS ik ON (k.id=ik.keyword_id && ik.product_id='3') GROUP BY ik.product_id
[/code]

[fenway]

Could you post some mysqldump output (structure + data) so that I have some sample data to work with?  Also, let me know what output you desire.

[aunquarra]

Sure... Here's the mysqldump:
[code]
CREATE TABLE `items_keywords` (
  `id` int(11) NOT NULL auto_increment,
  `item_id` int(11) NOT NULL,
  `keyword_id` int(11) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='keywords assigned to a given item';
CREATE TABLE `keywords` (
  `id` int(11) NOT NULL auto_increment,
  `keyword` varchar(20) NOT NULL,
  `desc` varchar(100) NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `keyword` (`keyword`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='assignable keywords';

INSERT INTO `keywords` VALUES (1,'brown','This is the color brown. You know, like chocolate.'),
  (2,'black','This is the color black. Licorice anyone?'),
  (3,'orange','This is the fruit orange. Bet you thought it was going to be the color...'),
  (4,'green','This is the color green. Is it envy, lust, or money?');
INSERT INTO `items_keywords` VALUES (1,1,2),(2,1,4),(3,2,3),(4,3,1);[/code]

And with that data, here's how my query stands now:
[code]
mysql> SELECT k.keyword, k.desc, COUNT(*) AS counter FROM keywords AS k LEFT JOIN items_keywords AS ik ON (k.id=ik.keyword_id && ik.item_id='1') GROUP BY ik.item_id;
+---------+----------------------------------------------------+---------+
| keyword | desc                                              | counter |
+---------+----------------------------------------------------+---------+
| brown  | This is the color brown. You know, like chocolate. |      2 |
| black  | This is the color black. Licorice anyone?          |      2 |
+---------+----------------------------------------------------+---------+
2 rows in set (0.00 sec)
[/code]

And here's what I'm going for:
[code]
mysql> SELECT <magic query here>;
+---------+---------------------------------------------------------------------------+---------+
| keyword | desc                                                                      | counter |
+---------+---------------------------------------------------------------------------+---------+
| brown  | This is the color brown. You know, like chocolate.                        |      0 |
| black  | This is the color black. Licorice anyone?                                |      1 |
| orange  | This is the fruit orange. Bet you thought it was going to be the color... |      0 |
| green  | This is the color green. Is it envy, lust, or money?                      |      1 |
+---------+---------------------------------------------------------------------------+---------+
4 rows in set (0.00 sec)
[/code]
where the 'counter' is the number of times the keyword_id is in the items_keywords table WHERE item_id=1

Thanks again for the help. This has been a real learning experience.

[fenway]

I understand -- but you can't do this without a third join, because you lose the uniqueness with the group by.

[aunquarra]

Meh. Okay, so I'll just do it with php. I can deal with the count not being sortable.

Thanks for all your help! I actually understand joins a lot better now (so much so that I've used them on several other pieces of this project already).

[fenway]

It's not being sortable per se that's the problem -- you can't count them at all.

[aunquarra]

Right, but the benefit of trying to find a way to do it in mySQL rather than PHP is that sorting columns in mySQL is much easier (on both the programmer and the server). ;)

Since it's not possible, I'll just have everything else be sortable and just plug the count values in with PHP.

[fenway]

It's your decision.. it's more complex in MySQL, you'd probably need a dervied table or something similar... however, you're correct, since you'd be returning the same number of rows (sort of), you could do the counting in PHP as well.