Jump to content


Photo

selecting a count from another table


  • Please log in to reply
17 replies to this topic

#1 aunquarra

aunquarra
  • Members
  • PipPipPip
  • Advanced Member
  • 41 posts
  • LocationDallas, Texas

Posted 07 August 2006 - 03:23 PM

Basically, I have two tables like this:

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

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.

#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 07 August 2006 - 04:17 PM

Try the following (untested):

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

Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#3 aunquarra

aunquarra
  • Members
  • PipPipPip
  • Advanced Member
  • 41 posts
  • LocationDallas, Texas

Posted 07 August 2006 - 06:45 PM

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

Thanks!

#4 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 07 August 2006 - 10:55 PM

Scaling shouldn't be an issue, provided the necessary columns are indexed.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#5 aunquarra

aunquarra
  • Members
  • PipPipPip
  • Advanced Member
  • 41 posts
  • LocationDallas, Texas

Posted 08 August 2006 - 12:30 PM

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):
Query: SELECT ??????   ik.item_id=3   ?????
+----+---------+----------+
| id | keyword | selected |
+----+---------+----------+
|  1 | brown   |        1 |
|  2 | black   |        0 |
+----+---------+----------+

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

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

#6 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 08 August 2006 - 02:30 PM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#7 aunquarra

aunquarra
  • Members
  • PipPipPip
  • Advanced Member
  • 41 posts
  • LocationDallas, Texas

Posted 08 August 2006 - 03:02 PM

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.

#8 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 08 August 2006 - 03:21 PM

Well, you could move that where condition to the ON clause, which would have it evaluated _before_ the column values got NULLed out.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#9 aunquarra

aunquarra
  • Members
  • PipPipPip
  • Advanced Member
  • 41 posts
  • LocationDallas, Texas

Posted 08 August 2006 - 04:02 PM

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.

#10 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 08 August 2006 - 09:05 PM

Then I'll have to take another look...
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#11 aunquarra

aunquarra
  • Members
  • PipPipPip
  • Advanced Member
  • 41 posts
  • LocationDallas, Texas

Posted 09 August 2006 - 06:22 PM

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

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


#12 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 09 August 2006 - 07:33 PM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#13 aunquarra

aunquarra
  • Members
  • PipPipPip
  • Advanced Member
  • 41 posts
  • LocationDallas, Texas

Posted 09 August 2006 - 08:09 PM

Sure... Here's the mysqldump:
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);

And with that data, here's how my query stands now:
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)

And here's what I'm going for:
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)
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.

#14 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 09 August 2006 - 10:37 PM

I understand -- but you can't do this without a third join, because you lose the uniqueness with the group by.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#15 aunquarra

aunquarra
  • Members
  • PipPipPip
  • Advanced Member
  • 41 posts
  • LocationDallas, Texas

Posted 10 August 2006 - 12:08 PM

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

#16 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 10 August 2006 - 02:53 PM

It's not being sortable per se that's the problem -- you can't count them at all.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#17 aunquarra

aunquarra
  • Members
  • PipPipPip
  • Advanced Member
  • 41 posts
  • LocationDallas, Texas

Posted 10 August 2006 - 02:59 PM

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.

#18 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 10 August 2006 - 03:13 PM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users