Styjun

I have a quick question in regards to using sql-server.
I have two tables, one for insurers, and another for appointments. My goal is to create a query where I grab all the appointments that use two specific insurers. For example, I want all the assignments that use state farm and USAA as their insurer.

SELECT TOP (1000) [Id]
 ,[Name]
 ,[AddressOne]
 ,[AddressTwo]
 ,[City]
 ,[State]
 ,[Zip]
 ,[PrimaryPhone]
 ,[SecondaryPhone]
 ,[Fax]
 ,[InsurerStatusId]
 ,[InsuranceGroupId]
 ,[ModifiedDate]
 ,[ModifiedBy]
 ,[ReportInsuranceName]
 ,[ReportInsuranceId]
 FROM [AssignmentManagement].[dbo].[Insurers]

SELECT TOP (1000) [Id]
 ,[AppointmentTypeId]
 ,[AssignmentId]
 ,[StaffId]
 ,[CalendarId]
 ,[AppointmentDate]
 ,[ScheduledDate]
 ,[RequestedAppointmentDate]
 ,[Notes]
 ,[Hour]
 ,[Minute]
 ,[DayOfTheWeek]
 ,[CenterId]
 ,[AppointmentStatusId]
 ,[RentalCar]
 ,[ScheduledBy]
 FROM [AssignmentManagement].[dbo].[Appointments]

SELECT TOP (1000) [Id]
 ,[InsurerId]
 ,[RepairCategoryId]
 ,[AssignmentStatusId]
 ,[OriginalCenterId]
 ,[OriginalStaffId]
 ,[LossCategoryId]
 ,[ClaimNumber]
 ,[PolicyNumber]
 ,[LossDate]
 ,[TotalLossIndicator]
 ,[Source]
 ,[PrimaryCustomerType]
 ,[InsuredCustomerId]
 ,[OwnerCustomerId]
 ,[ClaimCustomerId]
 ,[CustomerServiceRepresentativeId]
 ,[TransactionId]
 ,[Towing]
 ,[RentalCar]
 ,[IsDriveIn]
 ,[Deductible]
 ,[DeductibleStatus]
 ,[PhotosOnly]
 ,[LeftMessageSequence]
 ,[VehicleYear]
 ,[VehicleMake]
 ,[VehicleMakeDescription]
 ,[VehicleModel]
 ,[VehicleColor]
 ,[VIN]
 ,[Odometer]
 ,[LicenseNumber]
 ,[LicenseState]
 ,[VehicleLocationName]
 ,[VehicleAddressOne]
 ,[VehicleAddressTwo]
 ,[VehicleCity]
 ,[VehicleState]
 ,[VehicleZip]
 ,[VehiclePhone1]
 ,[VehiclePhone1Ext]
 ,[VehiclePhone2]
 ,[VehiclePhone2Ext]
 ,[VehicleContactFirstName]
 ,[VehicleContactLastName]
 ,[VehicleCondition]
 ,[VehicleNotes]
 ,[Impact1]
 ,[Impact2]
 ,[ClaimOfficeName]
 ,[AgentName]
 ,[AgentFirstName]
 ,[AgentLastName]
 ,[AgentAddressOne]
 ,[AgentAddressTwo]
 ,[AgentCity]
 ,[AgentState]
 ,[AgentZip]
 ,[AgentLicenseNumber]
 ,[AgentPhone]
 ,[AgentPhoneExt]
 ,[AgentFax]
 ,[AgentEmail]
 ,[PriorDamage]
 ,[Notes]
 ,[DamageNotes]
 ,[SpecialInstructions]
 ,[DispatchNotes]
 ,[LossNotes]
 ,[OtherNotes]
 ,[AssignmentDate]
 ,[ReportDate]
 ,[NextCallDate]
 ,[CreateDate]
 ,[CreatedBy]
 ,[IsEstimateMatch]
 ,[IsROMatch]
 ,[IsDriveable]
 ,[ModifiedDate]
 ,[UpdatedBy]
 ,[AccessedBy]
 ,[LegacyAsgnNavId]
 ,[LegacyAsgnNo]
 ,[AssignmentStatusReasonId]
 ,[RentalAgencyId]
 ,[Estimate_Id]
 ,[NextCallDateUpdatedBy]
 ,[RentalReservationNumber]
 ,[IsHail]
 ,[InitialOriginatingCenterId]
 ,[UnscheduledDropId]
 ,[UnscheduledExplanation]
 ,[RepairLevelId]
 ,[AdjusterFirstName]
 ,[AdjusterLastName]
 ,[AdjusterPhoneExt]
 ,[AdjusterPhoneNumber]
 ,[VehicleTrim]
 ,[VehicleOptions]
 ,[AdditionalInformation]
 ,[DeliveryLocation]
 ,[DeliveryCenterId]
 ,[RequestedAppointmentDate]
 FROM [AssignmentManagement].[dbo].[Assignments]

It sounds like the appointment [AssignmentId] relates to the insurer [Id] field:

-- Assuming a.AssignmentId = i.Id
-- Assuming USAA's ID = 12345 and State Farm's ID = 54321:

SELECT i.[Id]
 ,i.[Name]
 ,i.[AddressOne]
 ,i.[AddressTwo]
 ,i.[City]
 ,i.[State]
 ,i.[Zip]
 ,i.[PrimaryPhone]
 ,i.[SecondaryPhone]
 ,i.[Fax]
 ,i.[InsurerStatusId]
 ,i.[InsuranceGroupId]
 ,i.[ModifiedDate]
 ,i.[ModifiedBy]
 ,i.[ReportInsuranceName]
 ,i.[ReportInsuranceId]
 ,a.[Id]
 ,a.[AppointmentTypeId]
 ,a.[AssignmentId]
 ,a.[StaffId]
 ,a.[CalendarId]
 ,a.[AppointmentDate]
 ,a.[ScheduledDate]
 ,a.[RequestedAppointmentDate]
 ,a.[Notes]
 ,a.[Hour]
 ,a.[Minute]
 ,a.[DayOfTheWeek]
 ,a.[CenterId]
 ,a.[AppointmentStatusId]
 ,a.[RentalCar]
 ,a.[ScheduledBy]
FROM [AssignmentManagement].[dbo].[Insurers] i
 JOIN [AssignmentManagement].[dbo].[Appointments] a
 ON a.AssignmentId = i.Id
WHERE i.Id IN(12345, 54321)
ORDER BY i.ID, a.[RequestedAppointmentDate];

You might have to join insurer table with appointments twice if you want to make sure you're only picking appointments that have both the insurers

 SELECT
 TOP (1000) A.[Id] ,
 A.[AppointmentTypeId] ,
 A.[AssignmentId] ,
 A.[StaffId] ,
 A.[CalendarId] ,
 A.[AppointmentDate] ,
 A.[ScheduledDate] ,
 A.[RequestedAppointmentDate] ,
 A.[Notes] ,
 A.[Hour] ,
 A.[Minute] ,
 A.[DayOfTheWeek] ,
 A.[CenterId] ,
 A.[AppointmentStatusId] ,
 A.[RentalCar] ,
 A.[ScheduledBy] ,
 I1.Name,
 I2.Name 
 FROM
 [AssignmentManagement].[dbo].[Appointments] A 
 left join
 [AssignmentManagement].[dbo].[Insurers] I1 
 on A.StaffId = I1.Id 
 and I1.Name = 'State Farm' 
 left join
 [AssignmentManagement].[dbo].[Insurers] I2 
 on A.StaffId = I2.Id 
 and I2.Name = 'USAA' 
 where
 I1.Name is not NULL 
 and I2.Name is not NULL