Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mattzyzy
Starting Member
4 Posts |
 Posted - 2008-02-10 : 14:10:51
|
| --------------------SELECT id_tender,count(*) as no_of_app FROM applytender WHERE id_tender IN (select id from tenders where owner1 = 2) GROUP BY id_tender-------------------- above sql query is OK and will return :id_tender | no_of_app1 | 33 | 6but above query is not useful as the results of id_tender column are foreign key values . So when change it to the below query (using Inner Join)to retrieve its details from another table , tenders :-------------------------------SELECT applytender.id_tender,count(*) as no_of_app,tenders.titlename FROM applytender JOIN tenders ON applytender.id_tender=tenders.id WHERE id_tender in (select id from tenders where owner1 = 2) GROUP BY id_tender------------------------------an error message displays in the output box , saying : "tenders.titlename contains no aggregate functions....."What's the problem? How to fix this query ?----------merci |
|
|
hey001us
Posting Yak Master
185 Posts |
Posted - 2008-02-10 : 15:13:36
|
| SELECT applytender.id_tender,count(*) as no_of_app,tenders.titlename FROM applytender JOIN tenders ON applytender.id_tender=tenders.id WHERE id_tender in (select id from tenders where owner1 = 2) GROUP BY id_tend, tenders.titlenamehey |
 |
|
|
mattzyzy
Starting Member
4 Posts |
Posted - 2008-02-10 : 16:03:29
|
| Thank you very much.I finally know where I got it wrong . The query you provided, the logic the way I wanted to, is right , thus the problem is solved. Can continue with my work now........----------merci |
|
|
|
|
|
|
|
|
a select using join,subquery,and group by problem