subquerys

[Rodolphe Toots]

hi all!

i have a question regardin subselects or subqueries in the SELECT statement

i searched the manual yesterday but did not find any answer to this

in my SELECT statement i would like to do a subselect like this
SELECT t1.field1, t1.field2, (SELECT count(t2.field1) FROM table2 t2 WHERE t2.foreignKeyID
= t1.primaryKeyID)
FROM table1
WHERE...

does anyone know if this is possible?

also..i tried to rewrite my query and use a left JOIN to accomplish what i wanted
(what i really want is to count the number of topics in a forum to display in my list of
available forums)

so..i rewrote it like this
SELECT forum.forumName, COUNT(topics.topicID) AS nrOfTopcs
FROM forum
LEFT JOIN topics ON topics.forumID = forum.forumID

this worked..but the nrOfTopics counted WRONG!!??
on one of the listed forums i always got a topiccount of 1 more than there was topics..but
the others where correct

does not COUNT work the same way in mySQL and MS SQL??

any help is much appreciated

/regards Rudde