count problem

[birbal]

is there any way to use THE FOLLOWING QUERY IN JOIN?i mean something like substitute of where clause with count in join.

SELECT COUNT(*) FROM table1 WHERE COL=1

i am not sure how to explain that. hope i am able to clarify my question.thank you!

[justsomeguy]

What exactly are you trying to do?

[birbal]

actually i am trying to do something like this..1) collect forum name,id....[table forum]2) collect topic count of each forum...[table forum]3) collect last post id,hosted topic id,creator using MAX(createtime)....[table post]4) link with hosted topic id with the topic id [in table topic] and show the topic name.and list up the all forums. i cant manage to do it in one query. as far i manage to do it individualy. when i tring to join all things its wireing and i giving undesired value. suppose this is returning the list of forum name id total topic in that forum

SELECTa.fida.name,a.description,COUNT(b.tid) AS totaltopicFROM forum aLEFT OUTER JOIN topic bON a.fid=b.forumGROUP BY a.fid

it is doin 1,2..now i cant find a way to conjugated 3,4 anyhow.it is little bit complicated...its little hard for me to describe it in english. hope i am able to clarify it.

[birbal]

i come up with this final query (main part)

SELECTa.fid,a.name,a.description,a.owner AS forumlead,a.status AS fstatus,b.tid,b.subject,b.owner AS towner,b.status AS topicstatus,c.owner AS powner,c.status AS pstatus,MAX(d.born) AS lastpost,COUNT(e.tid) AS totaltopicFROM forum aLEFT OUTER JOIN topic bON a.fid=b.forumLEFT OUTER JOIN post cON b.tid=c.topicLEFT OUTER JOIN post dON c.pid=d.pidLEFT OUTER JOIN topic eON e.tid=b.tidGROUP BY a.fid

it is showing the topic count and last message in post. but problem is it cant relate that which is last post's Hosted topic id HOW to do it?need some help. and corrections if needed anywhere.

[justsomeguy]

Can't you add c.topic to the list of fields to get?

[birbal]

Can't you add c.topic to the list of fields to get?

i was looking for you from yesterday. i stucked badly here. cant find a way.c.topic is not returing the topic id of last post.(even same with d.born)it is returning the first topic id of particular forum.MAX(d.born) is returning the last post time for a particular forum but it is not returning the associated d.pid with born. instead of that it is returning the first topic id particular forum.

[birbal]

Can't you add c.topic to the list of fields to get?

i was looking for you from yesterday. i stucked badly here. cant find a way.c.topic is not returing the topic id of last post.(even same with d.born)it is returning the first topic id of particular forum.MAX(d.born) is returning the last post time for a particular forum but it is not returning the associated d.pid with born. instead of that it is returning the first topic id particular forum.

[justsomeguy]

Add all of the other fields to the group by clause. You should be grouping by all fields that are not included in an aggregate function.

[birbal]

Add all of the other fields to the group by clause. You should be grouping by all fields that are not included in an aggregate function.

sorry i did not get it..what do you mean?

Add all of the other fields to the group by clause

which one you are reffering?

[justsomeguy]

When you're joining tables and using some of them in aggregate functions, you need to list all of the columns that are not used in the aggregate functions in the group by clause. That means that the group by clause needs to contain every field you're selecting that is not used in an aggregate function.

[birbal]

ok..i did as you reffer

GROUP BYa.fid,a.name,a.description,a.owner,a.status,b.tid,b.subject,b.owner,b.status,c.owner,c.status

but it is returning more than one row for each forum. what to do?i guess it can be done with corelated queries though i did not figure it out correctly. is there any way to solve it using corelated query?

[justsomeguy]

You could use temporary tables instead. e.g.:

LEFT OUTER JOIN   (SELECT MAX(born) AS lastpost, pid FROM post GROUP BY pid) AS dON c.pid=d.pid

Then in the select list you would just get d.lastpost instead of doing the aggregate there.