Solved

display orphan records

Posted on 2014-04-28
7
222 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
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…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how the fundamental information of how to create a table.

932 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

11 Experts available now in Live!

Get 1:1 Help Now