?
Solved

display orphan records

Posted on 2014-04-28
7
Medium Priority
?
235 Views
Last Modified: 2014-04-29
I have the following query:

select g.Agency,g.Goal,o.Objective,p.PI,a.[Activity Name] from tblOrgGoals G
Inner Join tblOrgObjectives O on G.GoalID = O.GoalID
Inner Join tblOrgPI P on O.ObjectiveID = P.ObjectiveID
Inner Join tblOrgActivities A on P.PIID = A.PIID


It only gives me records that are complete having all tables connected with actual records.  However, I have some tables that are incomplete.  For example, tblOrgActivities does not have and Activity for every record in tblOrgObjectives.

Is there a way to query all the above records and show even orphan records.  (See attachment highlighted in yellow)
example.xlsx
0
Comment
Question by:al4629740
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 41

Accepted Solution

by:
Sharath earned 800 total points
ID: 40028286
change INNER JOIN to LEFT JOIN
select g.Agency,g.Goal,o.Objective,p.PI,a.[Activity Name] from tblOrgGoals G 
LEFT Join tblOrgObjectives O on G.GoalID = O.GoalID
LEFT Join tblOrgPI P on O.ObjectiveID = P.ObjectiveID
LEFT Join tblOrgActivities A on P.PIID = A.PIID

Open in new window

0
 

Author Comment

by:al4629740
ID: 40028434
What is the difference between INNER JOIN and LEFT JOIN?

Does this display all possible records in all the tables?
0
 
LVL 41

Assisted Solution

by:Sharath
Sharath earned 800 total points
ID: 40028465
It displays all records from the first table and matching records from the rest of tables.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 25

Assisted Solution

by:DBAduck - Ben Miller
DBAduck - Ben Miller earned 800 total points
ID: 40028786
A LEFT JOIN matches all the rows in both tables and for those that do not exist in the RIGHT table, it shows NULLs.

So you can tell which ones do not match up by using something like this:

select g.Agency,g.Goal,o.Objective,p.PI,a.[Activity Name] 
from tblOrgGoals G 
LEFT Join tblOrgObjectives O on G.GoalID = O.GoalID
WHERE O.GoalID IS NULL

Open in new window


This will show you which OrgGoals rows do not have any rows in the OrgObjectives table.
0
 
LVL 15

Assisted Solution

by:Vikas Garg
Vikas Garg earned 400 total points
ID: 40029244
Hi,

The below query will give you the result as you require

select g.Agency,g.Goal,o.Objective,p.PI,a.[Activity Name] from tblOrgGoals G
Inner Join tblOrgObjectives O on G.GoalID = O.GoalID
LEFT Join tblOrgPI P on O.ObjectiveID = P.ObjectiveID
LEFT Join tblOrgActivities A on P.PIID = A.PIID

and if you want only those records which are not in  tblOrgPI  and tblOrgActivities then

select g.Agency,g.Goal,o.Objective,p.PI,a.[Activity Name] from tblOrgGoals G
Inner Join tblOrgObjectives O on G.GoalID = O.GoalID
LEFT Join tblOrgPI P on O.ObjectiveID = P.ObjectiveID
LEFT Join tblOrgActivities A on P.PIID = A.PIID

WHERE  p.PI IS NULL AND a.[Activity Name] IS null
0
 

Author Comment

by:al4629740
ID: 40030760
Could you help me a little more and show whats the difference between these two queries when it comes to the Join statements

select g.Agency,g.Goal,o.Objective,p.PI,a.[Activity Name] from tblOrgGoals G 
LEFT Join tblOrgObjectives O on G.GoalID = O.GoalID
LEFT Join tblOrgPI P on O.ObjectiveID = P.ObjectiveID
LEFT Join tblOrgActivities A on P.PIID = A.PIID

Open in new window


select g.Agency,g.Goal,o.Objective,p.PI,a.[Activity Name] from tblOrgGoals G
Inner Join tblOrgObjectives O on G.GoalID = O.GoalID
LEFT Join tblOrgPI P on O.ObjectiveID = P.ObjectiveID
LEFT Join tblOrgActivities A on P.PIID = A.PIID

Open in new window


select g.Agency,g.Goal,o.Objective,p.PI,a.[Activity Name] from tblOrgGoals G 
Inner Join tblOrgObjectives O on G.GoalID = O.GoalID
Inner Join tblOrgPI P on O.ObjectiveID = P.ObjectiveID
Inner Join tblOrgActivities A on P.PIID = A.PIID

Open in new window


To me the first two pull up the same data, but the third is different.
0
 
LVL 25

Assisted Solution

by:DBAduck - Ben Miller
DBAduck - Ben Miller earned 800 total points
ID: 40030795
Again, the first one will join tblOrgGoals with the other 3 and for every row in tblOrgGoals it will display a row, but for the columns that are in the tblOrgObjectives, tblOrgPI and tblOrgActivities it will show values for only those rows that have a match for each key in the ON clause and NULL for those values in those tables that do not have a match in the ON clause.

The 2nd one is close except that the first 2 tables will be joined by the ON clause and will technically be like 1 table with those rows that match the ON clause and then this table will be joined with the other 2 tables as above.

The 3rd one is INNER join only which means that it will only show rows that have all the JOIN ON clauses met with keys existing in both tables.  Any key that does not have a match on the left and right tables will be filtered out.
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question