Counting rows from table1 while querying table2

Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

With a MySQL set up I have 2 tables (posts and categories).

While querying 'categories' I need to also determine how many 'posts' were found in another table that relate the two. The relationship between the two is "catid" that exists inside 'posts' so I can determine where it belongs.

my $data = qq(SELECT id, catname, catdesc FROM categories WHERE 1  && 
+id, catid, text FROM posts);
# I know I'm far off
[download]

Comment on Counting rows from table1 while querying table2 Download Code

Replies are listed 'Best First'.
Re: Counting rows from table1 while querying table2 by graff (Chancellor) on Aug 28, 2007 at 03:01 UTC
I don't think this is a perl question, and anyway, you haven't quite given the right info to get the right answer. But I'll try guessing at an answer anyway... If the table "posts" has a field called "catid" such that many rows may have the same value in this field, and if the table "categories" also has a field called "catid" such that each catid value occurs in exactly one row, then you might be looking an sql statement like this: `select id, catname, catdesc, count(p.catid) from categories c, posts p where p.catid = c.catid group by p.catid` [download] But I'm just guessing, based on the fact that you say you want to "determine how many 'posts' were found..." If that doesn't work for you, you should consult a good resource for teaching SQL. Posting sql syntax questions to PerlMonks is okay if you do it in the Chatterbox. As a SoPW node, it's "off-topic".	[reply] [d/l]
Re: Counting rows from table1 while querying table2 by moritz (Cardinal) on Aug 28, 2007 at 07:49 UTC
Your question is very vague, but maybe you want something like that: `SELECT COUNT(c.id) FROM categories c, posts p WHERE c.id = p.catid` In Englisch that's "Count the catgeories for which a corresponding post exists" - if that's not what you want you have to clarify your question. Perl 6 in German -- Difficult Sudoku	[reply] [d/l]