LEFT JOIN several tables

I am trying to JOIN several tables that will show me what we have entered with Goals, Objectives, Activities, PI and Setbacks with the joining of the attached tables.  

Here is the sql string I am using:

select g.Agency,g.Goal,o.Objective,p.PI,P.Method,a.[ActivityName],P.[ProjectedHours],a.Data, s.ProgressSetback
from tblOrgGoals G 
LEFT Join tblOrgObjectives O on G.GoalID = O.GoalID 
LEFT Join tblOrgActivities A on O.ObjectiveID = A.ObjectiveID 
LEFT Join tblOrgMonthlyProgressSetbacks S on A.ActivityID = s.activityID
LEFT Join tblOrgPI P on P.ActivityID = A.ActivityID 
Where G.AgencyID = 74 And S.MonthlyID = 4 order by G.Agency,G.Goal

Open in new window


Am I on the right track?
Progress-Grid-Diagram.JPG
al4629740Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>Am I on the right track?
Tell us what kind of train you're trying to build here, and we'll comment on whether you're on the right track.

If your question is 'Did I pull off the LEFT JOINs correctly', the answer is yes, based on what you've told us so far, and no insight as to the table schemas so we can't really say if the JOIN .. ON columns are correct.
0
Dany BalianCTOCommented:
speaking of left joins, yes it seems correct, however.. why do you have agencyid in all tables? i guess you have a structural db design problem..
0
al4629740Author Commented:
Ultimately I am trying to places this output into a datagrid in vb6.  I want to edit the final column in the grid called ProgressSebacks.  However, I just realized that maybe I should not LEFT JOIN this line:

LEFT Join tblOrgMonthlyProgressSetbacks S on A.ActivityID = s.activityID

That way I can find all the records that actually exist in that particular table and then properly add a value

New query:

select g.Agency,g.Goal,o.Objective,p.PI,P.Method,a.[ActivityName],P.[ProjectedHours],a.Data, s.ProgressSetback
from tblOrgGoals G 
LEFT Join tblOrgObjectives O on G.GoalID = O.GoalID 
LEFT Join tblOrgActivities A on O.ObjectiveID = A.ObjectiveID 
Join tblOrgMonthlyProgressSetbacks S on A.ActivityID = S.ActivityID
LEFT Join tblOrgPI P on P.ActivityID = A.ActivityID 
Where G.AgencyID = 74 order by G.Agency,G.Goal

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

al4629740Author Commented:
I added an AgencyID in all tables for easy querying....not really necessary.
0
RayData AnalystCommented:
Keep in mind that a left join takes all records from "G" (first table) and adds records from 'left join' tables that match you 'on' condition.  If that is what you intended, then great.  You're doing good.  If not, let us know what you intended.

Also, without knowing you table relationships, I can be sure your joins are using the correct 'on' fields.  I will comment that I'd make the 'on' relationships for each new table point back to the "main" table of records (in this case: tblOrgGoals or "G") whenever possible.  Otherwise you might exclude a record unintentionally.
0
al4629740Author Commented:
Also, each ProgressSetback is tied to a MonthlyReportID in the tblOrgMonthlyReport...
0
RayData AnalystCommented:
In my previous comment meant can't be sure, not can be
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>I want to edit the final column in the grid called ProgressSebacks.
Also keep in mind that queries with multiple JOINs may not be editable, so you may not be able to pull this off with a single query.

I'm having a vague memory that all queries are not editable if they have three or more JOINs, but don't recall if that apples to LEFT JOIN instead of just an INNER JOIN.
0
Dany BalianCTOCommented:
you need to left join with progresssetback table because that's the table containing the needed data if i understood ur design correctly.
can you provide us with some sample data, and what your result is giving, and what your expected result should be so that we correct your issue.

try this sql to see if the results are better:

select g.Agency,g.Goal,o.Objective,p.PI,P.Method,a.[ActivityName],P.[ProjectedHours],a.Data, s.ProgressSetback
from tblOrgGoals G 
LEFT Join tblOrgObjectives O on G.GoalID = O.GoalID and g.agencyid=o.agencyid
LEFT Join tblOrgActivities A on O.ObjectiveID = A.ObjectiveID and g.agencyid=a.agencyid
LEFT Join tblOrgMonthlyProgressSetbacks S on A.ActivityID = s.activityID and g.agencyid=s.agencyid
LEFT Join tblOrgPI P on P.ActivityID = A.ActivityID and g.agencyid=p.agencyid
Where G.AgencyID = 74 And S.MonthlyID = 4 order by G.Agency,G.Goal

Open in new window

0
al4629740Author Commented:
What can I do to make it editable on the ProgressSetback column only?
0
Dany BalianCTOCommented:
if this is a bound grid, then you can't do it.. if it's unbound, then you just include the primary key of the progresssetback table in the query list, and fill it in the grid, and once the user saves, you issue an update command using that primary key!
0
al4629740Author Commented:
Your post yielded the same results
results.xlsx
0
al4629740Author Commented:
All the tables are related by foreign key.  What if I break the foreign key in the tblOrgMonthlyProgressSetbacks table?  Will that allow me to edit?  Currently the tblOrgMonthlyProgressSetbacks contains a foreign key to the tblOrgMonthlyReport table...
0
Dany BalianCTOCommented:
can you provide us with a sample data in each table? and your expected results?
0
al4629740Author Commented:
ok give me a sec
0
al4629740Author Commented:
0
Dany BalianCTOCommented:
you don't have anything with agencyid=74 in table tblOrgMonthlyProgressSetbacks

is this normal?
0
al4629740Author Commented:
There is no data in that table yet.  I typed those in the example.  All the other tables have actual current data.
0
al4629740Author Commented:
I will add sample data now with AgencyID = 74
0
al4629740Author Commented:
ProgressSetbackID	AgencyID	ActivityID	MonthlyID	ProgressSetback
1	74	1	3	test
2	74	2	4	another test
3	74	3	5	test again

Open in new window

0
Dany BalianCTOCommented:
ok, you're just trying to fill some data.. but let me explain something, so that you know how this works..

when you left join tables together, the results of both tables will multiply if records exist, and will nullify the remaining columns to the right if none exists.

look at the attached excel file to see how the joins on your sample data lead...


now, to solve your problem, you should fill your grid, but edit the columns in an unbound method.

how?
if a join exists, the column progresssetbackid will not be null and will have a specific value (example: 3 for test again), in this case, you can issue a db execute command:
update tblOrgMonthlyProgressSetbacks set progresssetback='my new progress setback' where id=3

however, if there's no join, then progresssetbackid is null and thus, you cannot edit that line, you have to insert a new line in the table.
to do that, you have to have the activityid, monthlyid, and agencyid from the grid line (of course the activityid,agencyid, and monthlyid columns have to be filled in the grid using your select)
and then issue an insert command
insert into tblOrgMonthlyProgressSetbacks (activityid, monthlyid, agencyid, progresssetback) values (3, 5, 74, 'new setback value to insert')

i hope this is clear now!
sample2.xlsx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
al4629740Author Commented:
if a join exists, the column progresssetbackid will not be null and will have a specific value (example: 3 for test again), in this case, you can issue a db execute command:
update tblOrgMonthlyProgressSetbacks set progresssetback='my new progress setback' where id=3

I notice you have vb6 programming experience.  Can I accomplish this insert from a datagrid using this query to the datagrid?

select g.Agency,g.Goal,o.Objective,p.PI,P.Method,a.[ActivityName],P.[ProjectedHours],a.Data, s.ProgressSetback
from tblOrgGoals G 
Join tblOrgObjectives O on G.GoalID = O.GoalID and g.agencyid=o.agencyid
Join tblOrgActivities A on O.ObjectiveID = A.ObjectiveID and g.agencyid=a.agencyid
Join tblOrgMonthlyProgressSetbacks S on A.ActivityID = s.activityID and g.agencyid=s.agencyid
Join tblOrgPI P on P.ActivityID = A.ActivityID and g.agencyid=p.agencyid
Where s.AgencyID = 74 And s.monthlyid = 3 And s.ActivityID = 1  order by G.Agency,G.Goal

Open in new window


Inserting my values in the column ProgressSetback? or I guess I can't because the query is a "bound method"
0
Dany BalianCTOCommented:
of course you can, you just use the select to fill the grid! with all needed values, but you need to select also the id values and put them in hidden fields.. and you can add the id using syntax like

 isnull(s.id,0) as progresssetbackid

in this case, this field will either have a 0 value or the id of the record to edit..
i haven't use datagrids for a while, i'm prefer listviews / flexgrids i also have some other grid components that i have purchased.

bound controls are very to edit when you have multiple joins..
in all cases, you need to have the primary key of the main table that you're gonna edit, so that the edit works.. but still, it's problematic, when this could be null
0
Dany BalianCTOCommented:
btw, if you attempt to edit a datagrid bound to multiple tables you will get the following error msg:
"Dynamic SQL generation is not supported against multiple base tables".

so, as i already noted, you should move away from bound controls and in this case use a different grid. Because datagrids are by design only bound.

just replace the grid with a listview for instance (or msflexgrid) and proceed like this:
dim rs as new adodb.recordset, li as listitem
rs.open "select ... ",db,adopenforwardonly,adlockreadonly
while not rs.eof
    set li=listview1.additem(,,rs("id"))
    li.subitems(1)=rs("column2")
    li.subitems(2)=rs("column3")
    li.subitems(3)=rs("setbackid")
...
rs.movenext
wend
rs.close
'you would have the full data in the grid, then you have to add the edit code manually, as i stated earlier
if listview1.selecteditem.subitem(3)=0 then 
  db.execute "insert into ..."
else
  db.execute "update ..."
end if

Open in new window


of course this code is not tested, it's just a push towards what you need to do..
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.