I have a table TeamWork, which is the master table. Then, I have TeamWorkMember which contains zero, one or more members associated to TeamWork. So, TeamWork.Numero=TeamWorkMember.NoTeamWork.

The goal is to list all TeamWork records where it has a specific TeamWorkMember record in it based on a member.

For example, I have this SQL:

SELECT TeamWork.Numero,TeamWorkMember.NoMember
FROM TeamWork
OUTER APPLY (SELECT TOP 1 TeamWorkMember.NoMember FROM TeamWorkMember WHERE TeamWork.Numero=TeamWorkMember.NoTeamWork) TeamWorkMember
WHERE TeamWorkMember.NoMember=958 

This is good. It gives me all TeamWork records having a child record with member 958. But, this is only valid if this is the first member assigned to the task. So, if for a TeamWork record, I have two child records, such as 421 and 958, it will not find it. This is because I have a TOP 1 in the OUTER APPLY and that will take only those ending on top of that INNER SQL.

If I do not include the TOP 1, it will work but it will not work if I have no WHERE clause because it will return multiple occurences of the master table based on the number of child records related to it.

This SQL has to be generic and work with or without the WHERE clause. So, if no WHERE clause is in there, I need to have all master records. Thus, if I have 911 records, I need to have 911 records. But, if there is a WHERE clause, it needs to give me only those where I have the member in the related records. So, if member 958 is within 12 records of the master table, than only those 12 records need to be selected.

Again, the SQL has to be generic with or without the where clause. Is there a way to achieve that?