Left Outer Join help needed

[ndnalibi]

Hello I have a stored porceedure below. I'm not getting results as expected. Basically I want every record from the OpenJob (O)table returned that matches the Where clause, then additional information when not empty from the other tables. If the other tables are empty for that job number, I don't get a result at all.

I've tried multiple combinations of Outer Joins, Distinct O.JobN, I've even created a view containing the Where statement and queried that.

What am I missing.

Thanks in advance for your time.

CREATE PROCEDURE [dbo].[Pplan_full] AS
Select O.JobN,
O.CustomerN,
O.Quantity,
O.DueDate,
O.JobDescription,
O.ProdPlanner,
J.StatusCode,
J.Note,
J.TransactionN,
J.Time,
MaxDateTable.LastDate,
JL.UpdateDate,
LaborCostCntr.LCCDescription,
JL.LLRecNum,
JL.StartTime
From OpenJob AS O
Left Outer Join JobStatus1 AS J
on O.JobN = J.JobN
Join JobLabor AS JL
on J.JobN = JL.JobN
Join (
Select JobN, Max(TransactionN) As MaxTransactionN
From JobStatus1
Group By JobN
) As MaxTable
On J.JobN = MaxTable.JobN
And J.TransactionN = MaxTable.MaxTransactionN
Join (
Select JobN, Max(LLRecNum) as LastDate
From JobLabor
Group By JobN
) As MaxDateTable
On JL.LLRecNum = MaxDateTable.LastDate
and JL.JobN = MaxDateTable.JobN
Join LaborCostCntr
On LaborCostCntr.LCCN = JL.LCCN
Where (O.JobStatus = '0' or O.JobStatus = '2' )
and O.DueDate BETWEEN dateadd(day,-7,getdate()) and dateadd(day,14,getdate())
and O.JobDescription not like 'WF%'

GO

 

[Ach005ki]

hi ndnalibi

I may be wrong here but is it not one of your innner joins which are causing your problem... because you've got MaxDateTable.LastDate in your select list which is being polled via an {LOJ} and then an {IJ}?

O {LOJ} J
J {IJ} MaxTable
J {IJ} MaxDateTable



Mark, somewhere near Blackburn Lancs!

 

[ndnalibi]

Hi Ach

I tried playing with that and ended up taking out "and JL.JobN = MaxDateTable.JobN" without any change. Note there is a J and a JL table.

The end result needs to be 1 row returned for every open jobN with the last TranactionN listed, which it does, now they are saying they need the open JobN's which do not have any transactions yet.

Currently there are 28 jobs with transactions, but 91 jobs total that are open.

Outer join seemed (on paper) to be the thing I need, but it isn't getting me the results I need.

Thanks,
Bill

 

[Ach005ki]

I think this example explains it better:

CREATE TABLE tempMHJOpen (jobno int PRIMARY KEY, jobname varchar(20) NULL)
CREATE TABLE tempMHJStat (jobno int PRIMARY KEY, jobstat varchar(20) NULL)
CREATE TABLE tempMHJAssg (jobno int PRIMARY KEY, jobassg varchar(20) NULL)

INSERT INTO  tempMHJOpen VALUES (1, '1st job')
INSERT INTO  tempMHJOpen VALUES (2, '2nd job')
INSERT INTO  tempMHJOpen VALUES (3, '3rd job')
INSERT INTO  tempMHJOpen VALUES (4, '4th job')
INSERT INTO  tempMHJOpen VALUES (5, '5th job')

INSERT INTO  tempMHJStat VALUES (1, 'Complete')
INSERT INTO  tempMHJStat VALUES (2, 'Suspended')
INSERT INTO  tempMHJStat VALUES (4, 'Complete')
INSERT INTO  tempMHJStat VALUES (5, 'Active')

INSERT INTO  tempMHJAssg VALUES (1, NULL)
INSERT INTO  tempMHJAssg VALUES (2, 'Mark H')
INSERT INTO  tempMHJAssg VALUES (5, 'Lee C')

SELECT * FROM tempMHJOpen
SELECT * FROM tempMHJStat
SELECT * FROM tempMHJAssg

SELECT O.jobno, O.jobname, J.jobstat
  FROM tempMHJOpen AS O
  LEFT OUTER
  JOIN tempMHJStat AS J
    ON O.jobno = J.jobno
 WHERE O.jobname <> '2nd job'
 
 SELECT O.jobno, O.jobname, J.jobstat, A.jobassg
  FROM tempMHJOpen AS O
  LEFT OUTER
  JOIN tempMHJStat AS J
    ON O.jobno = J.jobno
  JOIN tempMHJAssg AS A
    ON J.jobno = A.jobno
 WHERE O.jobname <> '2nd job'

[tt]
jobno jobname jobstat
----------- -------------------- --------------------
1 1st job Complete
3 3rd job NULL
4 4th job Complete
5 5th job Active

4 Row(s) affected
[/tt]

[tt]
jobno jobname jobstat jobassg
----------- -------------------- -------------------- --------------------
1 1st job Complete NULL
5 5th job Active Lee Cockings

2 Row(s) affected
[/tt]

Mark, somewhere near Blackburn Lancs!

 

[ndnalibi]

I started going down this path by creating a view (instead of a table) for the "where" statement.

I think I got it now by creating views and selecting from them for my stored proceedure.


Thanks for your help

Bill

 

[Ach005ki]

I've simplified your table joins as per below and to me these fields are the ones which would cause the rows not to be returned where the job has no transactions:

MaxDateTable.LastDate,
JL.UpdateDate,
LaborCostCntr.LCCDescription,
JL.LLRecNum,
JL.StartTime

From OpenJob AS O 
Left Outer Join JobStatus1 AS J 
on O.JobN = J.JobN

Join JobLabor AS JL
on J.JobN = JL.JobN

Join JobStatus1 AS MaxTable
On  J.JobN = MaxTable.JobN

Join LaborCostCntr AS LC
On LC.LCCN = JL.LCCN 

Join JobLabor AS MaxDateTable
On JL.LLRecNum = MaxDateTable.LastDate

i think the simplified hierarchy looks like this...

[tt]
O
{LOJ}
__J__
{IJ} {IJ}
_JL_ MaxTable
{IJ} {IJ}
LC MaxDateTable
[/tt]

I would recommend more {LOJ}s than {IJ}s ...

If you look at my example you will see that the last query only returns two rows because one of the fields is referenced in a table which is connected via an {IJ} to the 2nd table which is the one {OJ} linked to the primary table.

I'm no SQL authority, but a mere student myself, so I'd be very interested to see how your views are linked to get around this.

Mark, somewhere near Blackburn Lancs!

 

[ndnalibi]

Here is my solution - but be warned - I'm learning myself and this may not be the right way to do it. Feel free to warn/correct my convoluted code:

Like I said I used views - actually nested views

This one just gives me a view of all open jobs in the selected daterange

CREATE VIEW dbo.Pplan1
AS
SELECT     *
FROM         dbo.OpenJob O
WHERE     (JobStatus = '0' OR
                      JobStatus = '2') AND (DueDate BETWEEN DATEADD([day], - 7, GETDATE()) AND DATEADD([day], 14, GETDATE())) AND (JobDescription NOT LIKE 'WF%')

This one pulls the highest transaction number (which is a number that increments as it passes through each department)

CREATE VIEW dbo.maxTrans
AS
SELECT      JobN, MAX(TransactionN) AS MaxTransactionN
FROM         dbo.JobStatus1
GROUP BY JobN

This finds the last activity number which is used later to pull the activity description from another table

CREATE VIEW dbo.LLRnumb
AS
SELECT     JobN, MAX(LLRecNum) AS LastDate
FROM         dbo.JobLabor
GROUP BY JobN

This is the mother view. It selects based on the last 2 views being joined with the JobStatus1 table

CREATE VIEW dbo.PrePlan
AS
SELECT     J.Note, dbo.maxTrans.MaxTransactionN, J.[Time], JL.UpdateDate, JL.LLRecNum, JL.StartTime, J.JobN, dbo.LaborCostCntr.LCCDescription, 
                      J.StatusCode
FROM         dbo.JobStatus1 J RIGHT OUTER JOIN
                      dbo.maxTrans ON J.JobN = dbo.maxTrans.JobN AND J.TransactionN = dbo.maxTrans.MaxTransactionN INNER JOIN
                      dbo.JobLabor JL ON J.JobN = JL.JobN RIGHT OUTER JOIN
                      dbo.LLRnumb ON JL.LLRecNum = dbo.LLRnumb.LastDate INNER JOIN
                      dbo.LaborCostCntr ON JL.LccN = dbo.LaborCostCntr.LCCN
WHERE     (JL.UpdateDate > '1/1/2007')

Here is the stoed proceedure that uses the first view Pplan1 with an outer join on PrePlan, the last view

CREATE PROCEDURE [dbo].[Pplan_full] AS 
Select  Distinct O.JobN,
        O.CustomerN,
        O.Quantity,
        O.DueDate,
        O.JobDescription,
        O.ProdPlanner,
        P.StatusCode,
        P.Note,
        isnull( P.MaxTransactionN, 00) as MTN,
        P.Time,
        P.UpdateDate,
        isnull( P.LCCDescription, 'No Production Yet') as LCCDesc,
        P.LLRecNum,
        P.StartTime
From    Pplan1 AS O 
        Left Outer Join PrePlan as P 
          on O.JobN = P.JobN
GO

Seems to be giving me the right results. The person doing the report will be back on Monday, so we'll see if it's officially working then...or if there are more changes.

 

[ndnalibi]

Final:

I ended up combining the code for LLRnumb and MaxTrans into one view as such:

CREATE VIEW dbo.maxTrans
AS
SELECT      JL.JobN,
	MAX(TransactionN) AS MaxTransactionN, 
	MAX(LLRecNum) AS LastDate
FROM         dbo.JobLabor JL
	LEFT OUTER JOIN
	      dbo.JobStatus1 J
	on JL.JobN = J.JobN
GROUP BY JL.JobN

The reason was that we were missing a couple of jobs that had labor charged to it, but had no status codes. It was only about 2% of our open jobs.