[Resolved]SELECT COMMAND with multiple table

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

[Resolved]SELECT COMMAND with multiple table

Author

Topic

cutepraba
Yak Posting Veteran

53 Posts

Posted - 2008-07-26 : 08:09:18

I have two tables

sffolders
folderid foldername userid
1 Codes 1
2 Database 1
3 Goldscribe 1
4 Bomb 1
5 Links 1
6 email 1
7 Fun 1
8 personal 1

SFDATA
fileid folderid userid file
1 1 1 45215
2 2 1 45216
3 1 1 45217
4 4 1 45218
5 3 1 45219
6 6 1 45210
7 7 1 45211
8 8 1 45212
9 8 1 45213

Now i need to display the distinct folders of user 1, and count of files.

Folderid foldername filecount
1 Codes 2
2 dabase 0...........

I have tried with the follwoing code, can anyone please help me.

SELECT DISTINCT f.folderid, f.foldername, (select count(*) from u.userid where u.userid=f.userid and f.folderid=u.folderid)
FROM sffolders AS f, sfdata AS u;

____________
Praba

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-07-26 : 08:55:00

select f.folderid, f.foldername, count(*) as [filecount]
from [sffolders] f
inner join [sfdata] d on d.folderid = f.folderid and d.userid = f.userid
where d.userid = 1
group by f.folderid, f.foldername

Be One with the Optimizer
TG

cutepraba
Yak Posting Veteran

53 Posts

Posted - 2008-07-26 : 09:11:11

Wow, super.

I must learn very hard.

Thank you.

____________
Praba

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources