[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

display orphan records

Posted on 2014-04-28
7
Medium Priority
?
234 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

656 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