Solved

display orphan records

Posted on 2014-04-28
7
219 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 40

Accepted Solution

by:
Sharath earned 200 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 40

Assisted Solution

by:Sharath
Sharath earned 200 total points
ID: 40028465
It displays all records from the first table and matching records from the rest of tables.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 24

Assisted Solution

by:DBAduck - Ben Miller
DBAduck - Ben Miller earned 200 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 14

Assisted Solution

by:Vikas Garg
Vikas Garg earned 100 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 24

Assisted Solution

by:DBAduck - Ben Miller
DBAduck - Ben Miller earned 200 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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

744 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now