You're going to have to include the memberfl table twice. Once to pickup the relationship between the grpregfl and memberfl and again for the relationship between grpfl and memberfl. I'm guessing thatyour second query doesn't work due to:
> ON Memberfl.memberflid = Grpregfl.memberflid ;
> ON Memberfl.memberflid = Classregfl.memberflid ;
which requires that all three memberflid's contain the same value for the row to qualify. This means that the leader would have to be registered for the class and I would only expect the leaders to appear in the result set.
-Mike
>Can someone help me with the syntax of this SQL example using SQL Server files? I have three files members file, group registration file and group file. In my group file I have a field called leader that contains the group leaders memberflid. Is there any way in a single SQL statement to get the name of the leader also?
>
>This works.
>
>SELECT Classfl.descr as class, Grpfl.descr as group, Memberfl.fname, Grpfl.leader ;
> FROM ims!classfl ;
> INNER JOIN ims!classregfl;
> INNER JOIN ims!memberfl;
> INNER JOIN ims!grpregfl;
> INNER JOIN ims!grpfl ;
> ON Grpfl.grpflid = Grpregfl.grpflid ;
> ON Memberfl.memberflid = Grpregfl.memberflid ;
> ON Memberfl.memberflid = Classregfl.memberflid ;
> ON Classfl.classflid = Classregfl.classflid;
> WHERE Classfl.classflid = 3;
> ORDER BY Classfl.descr, Grpfl.descr, Memberfl.fname
>
>
>This does not work, can you make it work?
>
>SELECT Classfl.descr as class, Grpfl.descr as group, Memberfl.fname, Grpfl.leader, (sele fname FROM memberfl where memberid = grpfl.leader) as LeaderName ;
> FROM ims!classfl ;
> INNER JOIN ims!classregfl;
> INNER JOIN ims!memberfl;
> INNER JOIN ims!grpregfl;
> INNER JOIN ims!grpfl ;
> ON Grpfl.grpflid = Grpregfl.grpflid ;
> ON Memberfl.memberflid = Grpregfl.memberflid ;
> ON Memberfl.memberflid = Classregfl.memberflid ;
> ON Classfl.classflid = Classregfl.classflid;
> WHERE Classfl.classflid = 3;
> ORDER BY Classfl.descr, Grpfl.descr, Memberfl.fname
>
