I am trying to create a Select command to combine 3 tables.
GROUPS, I want to see every record of this table where the table meets the WHERE for the table CONTACTS, I want to see contacts that meet certain conditions, if there are no contacts I still want to see the GROUP records in the query GROUPCONTACTS, this table sits between GROUPS and CONTACTS to allow for a many-to-many relationship.
I have tried the following but it shows me every GROUPCONTACTS record instead of just those where there is a related CONTACTS that matches the query. I do not know if SQL allows for what I want.
Azure server running MSSQL server.

SELECT G.GroupID, GC.ContactID, C.ContactID, C.Status, C.Type
    FROM Groups G

LEFT JOIN GroupContacts GC
    ON GC.GroupID = G.GroupID

JOIN Contacts C
    ON C.ContactID = GC.ContactID
    AND C.Type = 'Manager'
    AND C.Status = 'Active'
 
WHERE G.Status = 'Active' AND G.Type = 'Physician'

I was hoping to see 1951 results showing 1 record per GROUPS whether or not there was a matching CONTACT. Instead I got 1550 results, excluding all GROUPS that didn't have a matching CONTACT.
I hope I am explaining this well enough How can I have the table Contacts JOIN with GroupContacts and then in turn have the results LEFT JOIN with GROUPS?
Sample source tables GROUP
| GroupID | Type | Name | Status |
| ------------ | ------------ | ------------ | ------------ |
| 1 | Physician | Drs. Bennett & Stein | Active |
| 2 | Physician | Drs. Kogan & Larson | Inactive |
| 6 | Physician | Diagnostic Imaging | Active |
GROUPCONTACTS
| GroupContactID | GroupID | ContactID |
| ------------ | ------------ | ------------ |
| 13258 | 2227 | 124 |
| 13259 | 2305 | 138 |
| 13260 | 526 | 251 |
| 13261 | 2900 | 351 |
| 13262 | 1363 | 371 |
| 13263 | 2408 | 460 |
| 13264 | 417 | 511 |
CONTACTS
| ContactID | Type | First Name | Last Name | Status |
| ------------ | ------------ | ------------ | ------------ | ------------ |
| 375 | Physician | Mervyn L. | Elgart | Inactive |
| 376 | Physician | Stephen S. | Elgin | Inactive |
| 377 | Physician | Oscar | Ellison III | Active |
| 378 | Physician | Michael | Emmer | Active |
RESULTS (Ideal)
| GroupID | ContactID | ContactID | Status | Type |
| ------------ | ------------ | ------------ | ------------ | ------------ |
| 3177 | 36187 | 36187 | Active | Manager |
| 3178 | 36188 | 36188 | Active | Manager |
| 3179 | 36189 | 36189 | Active | Manager |
| 3180 | NULL | NULL | NULL | NULL |
If a GROUPS doesn't have a matching GROUPCONTACT record just show the last 4 fields as NULL. The reason for the 2 ContactIDs is just for testing purposes.

CREATE TABLE Groups ( GroupID int not null , Type nvarchar(50) not null , Name nvarchar(50) not null , Status nvarchar(50) not null ); INSERT INTO Groups (GroupID, Type, Name, Status) VALUES (1, 'Physician', 'Drs. Bennett & Stein', 'Active') , (2, 'Physician', 'Drs. Kogan & Larson','Inactive') , (6, 'Physician', 'Diagnostic Imaging', 'Active') ; CREATE TABLE GroupContacts ( GroupContactID int not null , GroupID int not null , ContactID int not null ); INSERT INTO GroupContacts (GroupContactID, GroupID, ContactID) VALUES (13258,2227,124) , (13259,2305,138) , (13260,526,251) , (13261,2900,351) , (13262,1363,371) , (13263,2408,460) , (13264,417,511) ; CREATE TABLE Contacts ( ContactID int not null , Type nvarchar(50) not null , FirstName nvarchar(50) not null , LastName nvarchar(50) not null , Status nvarchar(50) not null ); INSERT INTO Contacts (ContactID, Type, FirstName, LastName, Status) VALUES (375,'Physician','Mervyn L.','Elgart','Inactive') , (376,'Physician','Stephen S.','Elgin','Inactive') , (377,'Physician','Oscar','Ellison III','Active') , (378,'Physician','Michael','Emmer','Active') ; SELECT G.GroupID, GC.ContactID, C.ContactID, C.Status, C.Type FROM Groups G LEFT JOIN GroupContacts GC ON GC.GroupID = G.GroupID LEFT JOIN Contacts C ON C.ContactID = GC.ContactID AND C.Type = 'Manager' AND C.Status = 'Active' WHERE G.Status = 'Active' AND G.Type = 'Physician'

1条答案

按热度按时间

gkl3eglg1#

Next time, please post the CREATE TABLE and INSERT INTO setup statements. I created all those statements only to realize that the data in GroupContacts table doesn't match any of the sample data in Groups or Contacts . Fix the data. Include an example of data that is excluded but you think should be included. Then I'll update my answer.

GroupID	ContactID	ContactID	Status	Type

fiddle

赞(0）回复(0）举报 2022-12-10

SQL Server Query Select with multiple tables and needing Left Join

1条答案

相关问题

热门标签

最新问答