Solved

display orphan records

Posted on 2014-04-28
7
223 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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 15

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SqlDataBase 7 46
Addition to SQL for dynamic fields 6 36
SSRS 2012 r2 - Parm Drop Down has Date/Time 12 30
Find SQL query used by application 3 17
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

813 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

12 Experts available now in Live!

Get 1:1 Help Now