locked
getting data from parent child relationship tables RRS feed

  • 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....

    Friday, January 15, 2021 12:48 AM

All replies

  • 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

    Friday, January 15, 2021 10:11 AM
  • 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
    Friday, January 15, 2021 4:19 PM
  • 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

    Wednesday, January 20, 2021 4:05 AM