Jump to content

Archived

This topic is now archived and is closed to further replies.

aunquarra

selecting a count from another table

Recommended Posts

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.

Share this post


Link to post
Share on other sites
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]

Share this post


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

Thanks!

Share this post


Link to post
Share on other sites
Scaling shouldn't be an issue, provided the necessary columns are indexed.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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.

Share this post


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

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
Then I'll have to take another look...

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
I understand -- but you can't do this without a third join, because you lose the uniqueness with the group by.

Share this post


Link to post
Share on other sites
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).

Share this post


Link to post
Share on other sites
It's not being sortable per se that's the problem -- you can't count them at all.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.