Jump to content

selecting a count from another table


aunquarra

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

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.