[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1076
  • Last Modified:

Wildcard join problem in SQL

Dear Experts,

Apologies, this is a slightly complex one but here goes...

I'm trying to return all ObjectiveFeedback_Title based on a username.

I need to return ObjectiveFeedback_Titles from ALL sets, but the only place it's linked is here:

INNER JOIN CallBank ON ActivityObjectiveSets.ActivityObjectiveSet_ID = CallBank.ActivityObjectiveSet_ID

This is limiting my results to the ObjectiveFeedback_Title names where ObjectiveFeedback.ActivityObjectiveSet_ID=ActivityObjectiveSets.ActivityObjectiveSet_ID table.

How can I return ALL ObjectiveFeedback_Title regardless of what's in the ActivityObjectiveSet_ID field of the previous table?

THanks in advance.. Here's the entire query...

Nick

SELECT DISTINCT
ObjectiveFeedback.ObjectiveFeedback_ID,
ObjectiveFeedback.ObjectiveFeedback_Title,
ResponseTypes.ResponseType_Title,
ResponseTypes.ResponseType_ID,
ObjectiveSetObjectives.Objective_Id
FROM ActivityObjectiveSets
INNER JOIN CallBank ON ActivityObjectiveSets.ActivityObjectiveSet_ID = CallBank.ActivityObjectiveSet_ID
INNER JOIN ObjectiveSetObjectives ON ActivityObjectiveSets.ActivityObjectiveSet_ID = ObjectiveSetObjectives.ActivityObjectiveSet_ID
INNER JOIN ObjectiveFeedback
INNER JOIN ResponseTypes ON ObjectiveFeedback.ResponseType_ID = ResponseTypes.ResponseType_ID ON ObjectiveSetObjectives.Objective_Id = ObjectiveFeedback.Objective_ID
INNER JOIN Activities ON ActivityObjectiveSets.Activity_ID = Activities.Activity_ID
WHERE (CallBank.Username = @Username) AND (GETDATE() BETWEEN Activities.Activity_Start AND Activities.Activity_End)
0
nkewney
Asked:
nkewney
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:
perform a LEFT OUTER JOIN instead of an INNER JOIN
0
 
answer_meCommented:
Use a left join instead on inner join. e.g.
LEFT JOIN ObjectiveSetObjectives ON ActivityObjectiveSets.ActivityObjectiveSet_ID = ObjectiveSetObjectives.ActivityObjectiveSet_ID
0
 
nkewneyAuthor Commented:
This doesn't work as I want to ignore the value in ActivityObjectiveSet_ID so we can view all ObjectiveFeedback_Title for a particular user regardless of set.
0
 
BriniMavCommented:
Each time you do an inner join it narrows your result set. I'm not sure exactly what your data looks like but if you do what others have stated above it will give you all of the rows from the previous inner joins and the rows that match from the ObjectiveFeedback table. I've placed some sql below resembling what I think you are trying to do. Ask if you have any further questions.

SELECT DISTINCT
ObjectiveFeedback.ObjectiveFeedback_ID,
ObjectiveFeedback.ObjectiveFeedback_Title,
ResponseTypes.ResponseType_Title,
ResponseTypes.ResponseType_ID,
ObjectiveSetObjectives.Objective_Id
FROM ActivityObjectiveSets
INNER JOIN CallBank ON ActivityObjectiveSets.ActivityObjectiveSet_ID = CallBank.ActivityObjectiveSet_ID
INNER JOIN ObjectiveSetObjectives ON ActivityObjectiveSets.ActivityObjectiveSet_ID = ObjectiveSetObjectives.ActivityObjectiveSet_ID
LEFT OUTER JOIN ObjectiveFeedback ON ObjectiveSetObjectives.ActivityObjectiveSet_ID =
ObjectiveFeedback.ActivityObjectiveSet_ID
INNER JOIN ResponseTypes ON ObjectiveFeedback.ResponseType_ID = ResponseTypes.ResponseType_ID ON ObjectiveSetObjectives.Objective_Id = ObjectiveFeedback.Objective_ID
INNER JOIN Activities ON ActivityObjectiveSets.Activity_ID = Activities.Activity_ID
WHERE (CallBank.Username = @Username) AND (GETDATE() BETWEEN Activities.Activity_Start AND Activities.Activity_End)

0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now