getting data from parent child relationship tables

Question

User-153404742 posted

I think I'm almost there but confused on the fastest query.  It probably requires pivot but can't get it to get me the results

select fs.Fsid, fs.ParentId, s.sID, u.firstname, u.lastname, s.classInfo, fs.[FolderName]  from tblFiles fs
left join tblUserFiles uf on fs.Fsid = uf.Fsid
left join tblStudent s on uf.UserId = s.UserId
left join Users u on s.UserId = u.UserId
where fs.ParentId IS NULL OR fs.ParentId IN(select Fsid from tblFiles f where f.ParentId is null) 
order by fs.Fsid, fs.ParentId

This returns records like follows:

Fsid    parentId    sId        Firstname   Lastname           classInfo           FolderName

1         NULL      11111      FirstTest        LastTest           2019               FirstTest LastTest
2         NULL      22222      SecondTest    SecondLast    202-22            SecondTest SecondLast
3         NULL      33333      fname         lname              FS-329                 fname lname
4         3            33333       NULL            NULL             FS-329             Change of Status
6         3            33333      NULL             NULL             FS-329              Gab Testing
7         6              NULL       NULL          NULL              NULL                Change of Location
9         2              NULL       NULL          NULL              NULL                 Change of Grades

so if parentId is null, that's the top level/root folder.  If parentId has Id, that's the ID that's the fsId for the root file under which it is.  Example from above, there is a folder with NewFiles under which there is a folder called Change of Status....What I'm trying to do is get a result where I have Firstname, Lastname, fsID, classInfo and foldername for each record that relates to that user's file.   so above would be like the following:

FirstTest        LastTest         11111         2019               FirstTest LastTest
SecondTest    SecondLast   22222       202-22          SecondTest SecondLast

fname           lname    33333        FS-329                  fname lname

fname          lname          33333        FS-329           Change of Status

fname          lname          33333        FS-329           Gab Testing

and so on.....

so for each record where there is a parentId that's not null, I want to link it to the record where fsID is that parentId and get the firstname, lastname, sId, classInfo and so on....

Answer 1

User-939850651 posted

Hi inkaln,

According to your description, I tried to reproduced your problem but failed.

I guessed the table structure based on the query statement you provided, but it doesn't seem to be correct.

Then I add test data, this is my simple test (use the query statement you provided):

create table tblFiles (
Fsid int ,
ParentId int,
FolderName varchar(50)
)

insert into tblFiles  values (1,null,'FirstTest LastTest'),
(2,null,'SecondTest SecondLast'),(3,null,'fname lname'),
(4,3,'Change of Status'),(6,3,'Gab Testing'),
(7,6,'Change of Location'),(9,2,'Change of Grades')

create table tblUserFiles (
Fsid int,
UserId int
)

insert into tblUserFiles values (1,1),(2,2),(3,3),(4,3),(6,3),(7,1),(9,2)

create table tblStudent (
sID int ,
UserId int,
classInfo varchar(50)
)

insert into tblStudent values (11111,1,'2019'),(22222,2,'202-22'),(33333,3,'FS-329')

create table Users (
UserId int,
firstname varchar(50),
lastname varchar(50)
)

insert into Users values (1,'FirstTest','LastTest'),(2,'SecondTest','SecondLast'),(3,'fname','lname')

select fs.Fsid, fs.ParentId, s.sID, u.firstname, u.lastname, s.classInfo, fs.[FolderName]  from tblFiles fs
left join tblUserFiles uf on fs.Fsid = uf.Fsid
left join tblStudent s on uf.UserId = s.UserId
left join Users u on s.UserId = u.UserId
where fs.ParentId IS NULL OR fs.ParentId IN(select Fsid from tblFiles f where f.ParentId is null) 
order by fs.Fsid, fs.ParentId

Result:

So I think I may have some misunderstandings about the structure or data of this table, could you provide more details?

Best regards,

Xudong Peng

Answer 2

User-153404742 posted

so in the tblUserFiles table, you have inserted the following

insert into tblUserFiles values (1,1),(2,2),(3,3),(4,3),(6,3),(7,1),(9,2)

however, only the FsId 1,2 and 3 should have records linking in this table so it should have only (1,1,),(2,2) and (3,3)
as the other ones are the records underneath...so with ParentID3, that's link to the FsID 3....tblUserFiles has fsID records that have ParentID NULL

Answer 3

User-939850651 posted

Hi inkaln,

Thank you for you reply.

According to your description, I think you can use the union keyword to make separate queries in different situations.

Something like this:

create table tblFiles (
Fsid int ,
ParentId int,
FolderName varchar(50)
)

insert into tblFiles  values (1,null,'FirstTest LastTest'),
(2,null,'SecondTest SecondLast'),(3,null,'fname lname'),
(4,3,'Change of Status'),(6,3,'Gab Testing'),
(7,6,'Change of Location'),(9,2,'Change of Grades')

create table tblUserFiles (
Fsid int,
UserId int
)

insert into tblUserFiles values (1,1),(2,2),(3,3)

create table tblStudent (
sID int ,
UserId int,
classInfo varchar(50)
)

insert into tblStudent values (11111,1,'2019'),(22222,2,'202-22'),(33333,3,'FS-329')

create table Users (
UserId int,
firstname varchar(50),
lastname varchar(50)
)

insert into Users values (1,'FirstTest','LastTest'),(2,'SecondTest','SecondLast'),(3,'fname','lname')

select fs.Fsid, fs.ParentId, s.sID, u.firstname, u.lastname, s.classInfo, fs.[FolderName]  from tblFiles fs
left join tblUserFiles uf on fs.Fsid = uf.Fsid
left join tblStudent s on uf.UserId = s.UserId
left join Users u on s.UserId = u.UserId
where fs.ParentId IS NULL --OR fs.ParentId IN(select Fsid from tblFiles f where f.ParentId is null) 
union
select fs.Fsid, fs.ParentId, s.sID, u.firstname, u.lastname, s.classInfo, fs.[FolderName]  from tblFiles fs
left join tblUserFiles uf on fs.ParentId = uf.Fsid
left join tblStudent s on uf.UserId = s.UserId
left join Users u on s.UserId = u.UserId
where fs.ParentId IN(select Fsid from tblFiles f where f.ParentId is null) 
order by fs.Fsid, fs.ParentId

Result:

Hope this can help.

Best regards,

Xudong Peng