[RESOLVED] how to select count from 2ndary table on matches in primary table?

akremedy

I'm coming back after about a 4 year break, so even basic stuff is taking forever to re-learn - I have a feeling that this is going to be a dumb question.

Any suggestions on the best way to:

SELECT count(<other table entry id>) FROM <other table> WHERE <the entries in this table referencing other table id> = <entries in other table by other table entry id>

Basically, I just want to construct multiple rows of data that say: "here's the details of a Topic, and there are 10 (or whatever) Entries for this Topic" It seems like it should be simple enough.

Here's what I've written for the initial select - just getting the Topic data, now I need to incorporate some way of getting a count of Entries per Topic:

if(isset($_SESSION[user_id])) {
$query = "SELECT id,prmry,topic,author,date FROM topic ORDER BY date DESC LIMIT $offset, $limit";
}
$res = @($query, $sql);
while($row = mysql_fetch_array($res)) {

$id = $row[0];
$prmry = $row[1];
$topic = $row[2];
$author = $row[3];
$date = $row[4];

echo stuff here

}

MySQL = 5.0.24
PHP = 4.4.4

Thanks,
Adam

bogu

Try something like this:

SELECT t.id, COUNT(jt.joining_key) as nr_entries
FROM topic t
LEFT JOIN `joining_table` jt USING(joining_key)
GROUP BY t.id

akremedy

Thanks bogu - I think with your help I've got it figured out - I ended up doing the following:

Created foreign key on Entries (aka: "blog") table against the corresponding column (id) in the Topic table:

ALTER TABLE blog ADD CONSTRAINT fk_topic_id
FOREIGN KEY (topic) REFERENCES topic(id)

And then run the following query to fetch all the data needed out of Topic, plus a count on corresponding entries in Blog in the same results set:

SELECT t.id, t.prmry, t.topic, t.author, t.date, COUNT( bl.topic )
FROM topic t, blog bl
WHERE t.id = bl.topic
GROUP BY bl.topic

So the end result:

if(isset($_SESSION[user_id])) {
$query = "SELECT t.id, t.prmry, t.topic, t.author, t.date, COUNT( bl.topic )
FROM topic t, blog bl
WHERE t.id = bl.topic
GROUP BY bl.topic
ORDER BY date DESC LIMIT $offset, $limit";
}
$res = @($query, $sql);
while($row = mysql_fetch_array($res)) {

$id = $row[0];
$prmry = $row[1];
$topic = $row[2];
$author = $row[3];
$date = $row[4];
$count = $row[5];

echo stuff here

}

Thanks again...problem solved,
Adam