Multiple Inner Joins

[DebbieC]

I have a parent table that has a primary key of RequestID. I have 4 other tables that use the Request ID as a foreign key. Unfortunately during testing some records were entered into the parent table but subsequent records were not entered into any of the other tables. The way I originally had the stored procedure written it was returning all records in the parent table but now that is a problem since there aren't matching records. What I want is to return only the records that have records in the other tables. I tried doing multiple INNER JOINS and now it doesn't return any records.

This is what I tried but it doesn't return any records:

CREATE VIEW dbo.vSelectServiceLog
AS
SELECT TOP 100 PERCENT dbo.ServiceRequests.RequestID, dbo.ServiceRequests.Status, dbo.ServiceRequests.DateSent, dbo.ServiceRequests.ChangedBy, dbo.ServiceRequests.JurID,
dbo.Jurisdictions.FirstName + ' ' + dbo.Jurisdictions.LastName AS WRIBCoordinator

FROM dbo.ServiceRequests INNER JOIN
dbo.Jurisdictions ON dbo.ServiceRequests.JurID = dbo.Jurisdictions.JurID INNER JOIN
dbo.ReviewDraft ON dbo.ServiceRequests.RequestID = dbo.ReviewDraft.RequestID INNER JOIN
dbo.BookletMaster ON dbo.ServiceRequests.RequestID = dbo.BookletMaster.RequestID INNER JOIN
dbo.Scoring ON dbo.ServiceRequests.RequestID = dbo.Scoring.RequestID INNER JOIN
dbo.Feedback ON dbo.ServiceRequests.RequestID = dbo.Feedback.RequestID

Parent Table - ServiceRequests
Child Tables - ReviewDraft, BookletMaster, Scoring, Feedback

The Jurisdictions table doesn't contain any of the RequestID records. It is only to retrieve the Jurisdiction information.

I even tried another angle by trying the following but I got a lot of errors when I tried to save it:

SELECT dbo.ReviewDraft.RequestID, dbo.BookletMaster.RequestID, dbo.Scoring.RequestID, dbo.Feedback.RequestID, dbo.ServiceRequests.TestID, dbo.ServiceRequests.Status, dbo.ServiceRequests.StatusDate,
dbo.ServiceRequests.DateReceived, dbo.ServiceRequests.JurID, dbo.ServiceRequests.ReqType, dbo.ServiceRequests.DateNeeded,
dbo.ServiceRequests.DateSent, dbo.ServiceRequests.ChangedBy
WHERE dbo.ReviewDraft.RequestID = dbo.ServiceRequests.RequestID AND
dbo.BookletMaster.RequestID = dbo.ServiceRequests.RequestID AND
dbo.Scoring.RequestID = dbo.ServiceRequests.RequestID AND
dbo.Feedback.RequestID = dbo.ServiceRequests.RequestID


Can anyone help me?

 

[traingamer]

Use Left Joins rather then Inner Joins.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[DebbieC]

I tried the following but it only worked for the Review Draft table. It returned some records from ServiceRequest table that didn't have matching records in the other 3 tables.

FROM dbo.ServiceRequests INNER JOIN
dbo.Jurisdictions ON dbo.ServiceRequests.JurID = dbo.Jurisdictions.JurID INNER JOIN
dbo.ReviewDraft ON dbo.ServiceRequests.RequestID = dbo.ReviewDraft.RequestID LEFT JOIN
dbo.BookletMaster ON dbo.ServiceRequests.RequestID = dbo.BookletMaster.RequestID LEFT JOIN
dbo.Scoring ON dbo.ServiceRequests.RequestID = dbo.Scoring.RequestID LEFT JOIN
dbo.Feedback ON dbo.ServiceRequests.RequestID = dbo.Feedback.RequestID

 

[maswien]

I guess you want the record if it exists in any of the child tables. Try following:

CREATE VIEW dbo.vSelectServiceLog
AS
SELECT TOP 100 PERCENT dbo.ServiceRequests.RequestID, dbo.ServiceRequests.Status, dbo.ServiceRequests.DateSent,
dbo.ServiceRequests.ChangedBy, dbo.ServiceRequests.JurID,
dbo.Jurisdictions.FirstName + ' ' + dbo.Jurisdictions.LastName AS WRIBCoordinator

FROM dbo.ServiceRequests
where
dbo.ServiceRequests.JurID in ( select dbo.Jurisdictions.JurID from dbo.Jurisdictions )
or dbo.ServiceRequests.RequestID in ( select dbo.ReviewDraft.RequestID from dbo.ReviewDraft )
or dbo.ServiceRequests.RequestID in ( select dbo.BookletMaster.RequestID from dbo.BookletMaster )
or dbo.ServiceRequests.RequestID in ( select dbo.Scoring.RequestID from dbo.Scoring )
or dbo.ServiceRequests.RequestID in ( select dbo.Feedback.RequestID from dbo.Feedback )

 

[DebbieC]

Yes, you're right. That's exactly what I want. However it's returning over 37,000 records and should only return about 150.

It looks like we're on the right track though.

 

[maswien]

The problem is the Jurisdictions, I shouldn't include it in the select list, you can't include dbo.Jurisdictions.FirstName + ' ' + dbo.Jurisdictions.LastName in the query except table Jurisdictions can join to dbo.ServiceRequests using a column, so following SQL should be ok:

CREATE VIEW dbo.vSelectServiceLog
AS
SELECT TOP 100 PERCENT dbo.ServiceRequests.RequestID, dbo.ServiceRequests.Status, dbo.ServiceRequests.DateSent,
dbo.ServiceRequests.ChangedBy, dbo.ServiceRequests.JurID

FROM dbo.ServiceRequests
where
dbo.ServiceRequests.JurID in ( select dbo.Jurisdictions.JurID from dbo.Jurisdictions )
or dbo.ServiceRequests.RequestID in ( select dbo.ReviewDraft.RequestID from dbo.ReviewDraft )
or dbo.ServiceRequests.RequestID in ( select dbo.BookletMaster.RequestID from dbo.BookletMaster )
or dbo.ServiceRequests.RequestID in ( select dbo.Scoring.RequestID from dbo.Scoring )
or dbo.ServiceRequests.RequestID in ( select dbo.Feedback.RequestID from dbo.Feedback )

 

[DebbieC]

Thanks for the response. I tried it and it worked just like the first one, it works correctly for the first table (ReviewDraft) but continues to return all for the other tables.

Any more suggestions would be greatly appreciated.


Thanks.

 

[Pattycake245]

What about doing each join separately and then union-ing them all together.
something like:

CREATE VIEW dbo.vSelectServiceLog
AS
SELECT  RequestID, 
	Status, 
	DateSent, 
	ChangedBy, 
	JurID,
	WRIBCoordinator
from
(SELECT  a.RequestID, 
	a.Status, 
	a.DateSent, 
	a.ChangedBy, 
	a.JurID,
	b.FirstName + ' ' + b.LastName AS WRIBCoordinator
FROM    dbo.ServiceRequests a JOIN  dbo.Jurisdictions b
ON 	a.JurID = b.JurID 
UNION
SELECT  a.RequestID, 
	a.Status, 
	a.DateSent, 
	a.ChangedBy, 
	a.JurID,
	'' AS WRIBCoordinator
FROM    dbo.ServiceRequests a JOIN  dbo.ReviewDraft b
ON 	a.RequestID = b.RequestID 
UNION
SELECT  a.RequestID, 
	a.Status, 
	a.DateSent, 
	a.ChangedBy, 
	a.JurID,
	'' AS WRIBCoordinator
FROM    dbo.ServiceRequests a JOIN  dbo.BookletMaster b
ON 	a.RequestID = b.RequestID 
UNION
SELECT  a.RequestID, 
	a.Status, 
	a.DateSent, 
	a.ChangedBy, 
	a.JurID,
	'' AS WRIBCoordinator
FROM    dbo.ServiceRequests a JOIN  dbo.Scoring b
ON 	a.RequestID = b.RequestID 
UNION
SELECT  a.RequestID, 
	a.Status, 
	a.DateSent, 
	a.ChangedBy, 
	a.JurID,
	'' AS WRIBCoordinator
FROM    dbo.ServiceRequests a JOIN  dbo.Feedback b
ON 	a.RequestID = b.RequestID ) as results

Tim

 

[DebbieC]

It's returning duplicate records.

I tried the following and I could get it to work for each RIGHT JOIN but I can't get it to work for all of them.

SELECT dbo.ServiceRequests.RequestID, dbo.ServiceRequests.Status,
dbo.ServiceRequests.DateSent, dbo.ServiceRequests.ChangedBy, dbo.ServiceRequests.JurID,
dbo.Jurisdictions.FirstName + ' ' + dbo.Jurisdictions.LastName AS WRIBCoordinator

FROM dbo.ServiceRequests INNER JOIN
dbo.Jurisdictions ON dbo.ServiceRequests.JurID = dbo.Jurisdictions.JurID
RIGHT JOIN dbo.ReviewDraft ON dbo.ServiceRequests.RequestID = dbo.ReviewDraft.RequestID
RIGHT JOIN dbo.BookletMaster ON dbo.ServiceRequests.RequestID = dbo.BookletMaster.RequestID
RIGHT JOIN dbo.Scoring ON dbo.ServiceRequests.RequestID = dbo.Scoring.RequestID
RIGHT JOIN dbo.Feedback ON dbo.ServiceRequests.RequestID = dbo.Feedback.RequestID

I feel like I'm so close!!!! Any more suggestions?

 

[MDXer]

How about changing your inner joins to Left Outer joins.

Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[Pattycake245]

If it's returning duplicates do a group by. What do your duplicates look like?

Tim

 

[maswien]

Why it need return 150 rows? The original parent table has only 150 rows? If it does, that means a lot of records were entered into this table during the test, and these records do have the matched records in the child tables. Look the current parent table carefully and tell me what you want from this query.

 

[DebbieC]

I got it working after playing with it and used the following code:

SELECT dbo.ServiceRequests.RequestID, dbo.ServiceRequests.Status,
dbo.ServiceRequests.DateSent, dbo.ServiceRequests.ChangedBy, dbo.ServiceRequests.JurID,
dbo.Jurisdictions.FirstName + ' ' + dbo.Jurisdictions.LastName AS WRIBCoordinator

FROM dbo.ServiceRequests INNER JOIN dbo.Jurisdictions ON dbo.ServiceRequests.JurID = dbo.Jurisdictions.JurID
RIGHT JOIN dbo.ReviewDraft ON dbo.ServiceRequests.RequestID = dbo.ReviewDraft.RequestID
UNION

SELECT dbo.ServiceRequests.RequestID, dbo.ServiceRequests.Status,
dbo.ServiceRequests.DateSent, dbo.ServiceRequests.ChangedBy, dbo.ServiceRequests.JurID,
dbo.Jurisdictions.FirstName + ' ' + dbo.Jurisdictions.LastName AS WRIBCoordinator

FROM dbo.ServiceRequests INNER JOIN dbo.Jurisdictions ON dbo.ServiceRequests.JurID = dbo.Jurisdictions.JurID
RIGHT JOIN dbo.BookletMaster ON dbo.ServiceRequests.RequestID = dbo.BookletMaster.RequestID
UNION

SELECT dbo.ServiceRequests.RequestID, dbo.ServiceRequests.Status,
dbo.ServiceRequests.DateSent, dbo.ServiceRequests.ChangedBy, dbo.ServiceRequests.JurID,
dbo.Jurisdictions.FirstName + ' ' + dbo.Jurisdictions.LastName AS WRIBCoordinator

FROM dbo.ServiceRequests INNER JOIN dbo.Jurisdictions ON dbo.ServiceRequests.JurID = dbo.Jurisdictions.JurID
RIGHT JOIN dbo.Scoring ON dbo.ServiceRequests.RequestID = dbo.Scoring.RequestID
UNION

SELECT dbo.ServiceRequests.RequestID, dbo.ServiceRequests.Status,
dbo.ServiceRequests.DateSent, dbo.ServiceRequests.ChangedBy, dbo.ServiceRequests.JurID,
dbo.Jurisdictions.FirstName + ' ' + dbo.Jurisdictions.LastName AS WRIBCoordinator

FROM dbo.ServiceRequests INNER JOIN dbo.Jurisdictions ON dbo.ServiceRequests.JurID = dbo.Jurisdictions.JurID
RIGHT JOIN dbo.Feedback ON dbo.ServiceRequests.RequestID = dbo.Feedback.RequestID

Thanks for everyone's help!!!!