SELECT MULTIPLE TABLES - SQL Server Forums

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

SELECT MULTIPLE TABLES

Author

Topic

cutepraba
Yak Posting Veteran

53 Posts

Posted - 2008-07-28 : 08:08:16

I have three tables

folders
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
9 personal 2
10 personal 3
11 personal 4

DATA
fileid folderid userid fileno
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

Network
USERID - main userid
FUSERID -friends id
USERID FUSERID
1 2
2 2
1 4
3 4

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

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

It is confusing me for the past two day. please help

Thank you

____________
Praba

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-28 : 08:11:02

Try to write a query that uses a INNER JOIN.
What have you tried this far?

E 12°55'05.25"
N 56°04'39.16"

cutepraba
Yak Posting Veteran

53 Posts

Posted - 2008-07-28 : 08:15:15

I have already been suggested for INNER JOIN, now i am totaly out of contorl betwwen three tables and i am also confused how to relate the userid in Network table.

SELECT f.folderid, f.foldername, count(*) AS filecount
FROM sffolders AS f INNER JOIN sfurl AS d ON (d.folderid = f.folderid) AND (d.userid = f.userid)
WHERE d.userid = 1 GROUP BY f.folderid, f.foldername;

____________
Praba

dexter.knudson
Constraint Violating Yak Guru

260 Posts

Posted - 2008-07-28 : 08:19:47

Try something like this:
SELECT userid, folderid, foldername, count(fileno)
FROM Data
INNER JOIN Folders
ON Folders.folderid = Data.folderid
INNER JOIN Network
ON Network.userid = Folders.userid
WHERE Network.userid = 1
GOUP BY userid, folderid, foldername
ORDER BY userid, folderid, foldername

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-28 : 08:20:25

Something similar to this

SELECT		f.FolderID,
		f.FolderName,
		COUNT(d.FolderID)			-- Just in case there are empty folders
FROM		Network AS n
INNER JOIN	Folders AS f ON f.UserID = n.fUserID
LEFT JOIN	Data AS d ON d.FolderID = f.FolderID	-- Just in case there are empty folders
			AND d.UserID = f.fUserID
WHERE		n.UserID = 1
GROUP BY	f.FolderID,
		f.FolderName

E 12°55'05.25"
N 56°04'39.16"

cutepraba
Yak Posting Veteran

53 Posts

Posted - 2008-07-28 : 08:39:52

Both codes showing syntax error(missing operator)
Infact i dont want the folder tabel. i just want to know.

Now i am going with view, i am using MSSQL.

____________
Praba

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-28 : 15:23:46

Are you sure you are using MICROSOFT SQL SERVER?

E 12°55'05.25"
N 56°04'39.16"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-28 : 21:03:46

quote:
Originally posted by cutepraba

Both codes showing syntax error(missing operator)
Infact i dont want the folder tabel. i just want to know.

Now i am going with view, i am using MSSQL.

____________
Praba

Post the full error message

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources