Hi,
I have the following SQL statement and it brings 2 records per practice. One with the Practice Manager details and the second with the Lead GP details. All other columns are the same.
I would like to have instead ONLY one record with both the Practice Manager and Lead GP details. Any help will be appreciated
SELECT TOP (100) PERCENT Details.PracticeNumber, Picklist.ContactTitles.Description AS Title, Contacts.FirstName, Contacts.LastName, Contacts.Role,
Addresses.PracticeName, Addresses.Address1, Addresses.Address2, Addresses.Address3, Addresses.Town, Addresses.County, Addresses.PostCode,
Contacts.Email, Details.ContributionStatus
FROM Practice.AllCurrentDetails AS Details INNER JOIN
Practice.AllCurrentContacts AS Contacts ON Details.CetoPracticeId = Contacts.CetoPracticeId LEFT OUTER JOIN
Practice.AllCurrentAddresses AS Addresses ON Addresses.CetoPracticeId = Details.CetoPracticeId LEFT OUTER JOIN
Picklist.SystemProviders ON Details.SystemProvider = Picklist.SystemProviders.SystemProviderId LEFT OUTER JOIN
Picklist.ContactTitles ON Contacts.Title = Picklist.ContactTitles.ContactTitleId
WHERE (Details.SystemProvider = 1) AND (Details.ContributionStatus <> 4) AND (Contacts.Role = 'Practice Manager' OR
Contacts.Role = 'Lead GP')
ORDER BY Details.PracticeNumber
Thanks