Is this possible with one query?

• ryan-224331

  Grasshopper

  Points: 21

  More actions

  April 13, 2005 at 8:35 am

  #164038

  I want a query that will list a Particular TeamMember and everything from the File Table.  But what I can't figure out is I walso want to include any TeamMembers that are associated with the same FileNumber.  There is a one to many relation on FileNumber with File being the one and Team Having the Many.  Is this possible with one query?

  File FileNumber FileDescription FileStatus

  Team FileNumber TeamMember

   

  TeamMember1	FileNumber1	FileDescription	FileStatus	TeamMember2
  TeamMember1	FileNumber1	FileDescription	FileStatus	TeamMember3
  TeamMember1	FileNumber3	FileDescription	FileStatus	TeamMember4
  TeamMember1	FileNumber3	FileDescription	FileStatus	TeamMember8

   

• Ninja's_RGR'us

  SSC Guru

  Points: 294069

  More actions

  April 13, 2005 at 9:00 am

  #552447

  Yes it's possible... but why do you want something formatted like this??

  if object_id('TEAM') > 0

  drop table TEAM

  GO

  if object_id('FILES') > 0

  drop table FILES

  GO

  CREATE TABLE TEAM

  (

  FileNumber int not null,

  TeamMember varchar(50) not null

  )

  GO

  CREATE TABLE FILES

  (

  FileNumber int not null

  , FileDescription varchar(50) not null

  , FileStatus varchar(50) not null

  )

  GO

  Insert into Files (FileNumber, FileDescription, FileStatus) values (1, 'FileDescription', 'FileStatus')

  Insert into Files (FileNumber, FileDescription, FileStatus) values (3, 'FileDescription', 'FileStatus')

  Insert into Team (FileNumber, TeamMember) values (1, 'TeamMember1')

  Insert into Team (FileNumber, TeamMember) values (1, 'TeamMember2')

  Insert into Team (FileNumber, TeamMember) values (1, 'TeamMember3')

  Insert into Team (FileNumber, TeamMember) values (3, 'TeamMember1')

  Insert into Team (FileNumber, TeamMember) values (3, 'TeamMember4')

  Insert into Team (FileNumber, TeamMember) values (3, 'TeamMember8')

  GO

  Select T.TeamMember, F.FileNumber, F.FileDescription, F.FileStatus, T2.TeamMember

  FROM

  TEAM T inner join Files F on T.FileNumber = F.FileNumber LEFT OUTER join Team T2 on F.FileNumber = T2.FileNumber and T2.TeamMember T.TeamMember

  WHERE T.TeamMember = 'TeamMember1'

  GO

  drop table TEAM

  drop table FILES

  GO

• ryan-224331

  Grasshopper

  Points: 21

  More actions

  April 13, 2005 at 9:09 am

  #552452

  I want to create a report for certain TeamMembers.  They asked for this, I believe, because the secretaries can then see all of their Files as well as other individuals working on the same files (which I am guessing they already know who else works on that) so they can make changes or close files before we upgrade out of this legacy system.

  Thanks, that is a lot of help!