May 30, 2012 at 2:51 pm
SQL 2008 R2 64 bit Enterprise
I'm using 2 identical queries as sub queries and doing a left join between them. each query has a row count to give me sequence numbers based on the employee and date group, this is my join condition:
ON dep.EmpID = arr.EmpID
AND dep.FloatDepDate = arr.FloatDepDate
AND dep.Seq = arr.Seq+1
Using a LEFT JOIN so I should be getting a null row on the right side within each group of sequence numbers, date, emp.
In my outer most query if I use
Select * from ...
or
SELECT dep.*, arr.* FROM ...
the result set is only matched rows (basically acting like an inner join)
If I use:
SELECT dep.*, arr.empid FROM ....
then the result shows all left rows and a null empid from the right side on one row of each sequence group like it should.
I've never experienced this before or at least don't remember. Can anyone shed some light on why this is happening?
tom
May 30, 2012 at 3:10 pm
can you paste in your full query, also do you have a where clause as this can be a root cause to negating a left outer join
***The first step is always the hardest *******
May 30, 2012 at 3:29 pm
Ok here is the full statement.
SELECT *
FROM (SELECT cd.empid
,fd.dDateTimeLt
,fd.aDateTimeLt
,fd.ADep
,fd.AArr
,fd.FN
,fljd.DH
,ROW_NUMBER() OVER (PARTITION BY cd.empid,
FLOOR(CAST(fd.dDateTimeLt AS FLOAT)) ORDER BY cd.empid
, fd.dDateTimeLt
, fd.aDateTimeLt
, fd.ADep
, fd.AArr
, fd.FN) AS Seq
,FLOOR(CAST(fd.dDateTimeLt AS FLOAT)) AS FloatDepDate
FROM dbo.PFact cpf
INNER JOIN dbo.PFFact cpff
ON cpf.CSID = cpff.CSID
AND cpf.PSID = cpff.PSID
INNER JOIN dbo.DateDim d
ON cpff.FDSID = d.DateKey
INNER JOIN dbo.CPosDim pd
ON cpff.PosSID = pd.PosSID
AND cpff.PosSID = pd.PosSID
INNER JOIN dbo.FDim fd
ON cpff.FlSID = fd.FlSID
AND cpff.FlSID = fd.FlSID
INNER JOIN dbo.FLJdim fljd
ON cpff.LJSID = fljd.LJSID
AND cpff.LJSID = fljd.LJSID
LEFT OUTER JOIN dbo.CrDim cd
ON cpff.CSID = cd.CrSID
WHERE cpff.IsPrActive = 1
AND d.Next7Days = 'Next 7 Days'
AND pd.PosCd = 'R8') dep
LEFT JOIN (SELECT cd.empid
,fd.dDateTimeLt
,fd.aDateTimeLt
,fd.ADep
,fd.AArr
,fd.FN
,fljd.DH
,ROW_NUMBER() OVER (PARTITION BY cd.empid,
FLOOR(CAST(fd.dDateTimeLt AS FLOAT)) ORDER BY cd.empid
, fd.dDateTimeLt
, fd.aDateTimeLt
, fd.ADep
, fd.AArr
, fd.FN) AS Seq
,FLOOR(CAST(fd.dDateTimeLt AS FLOAT)) AS FloatDepDate
FROM dbo.PFact cpf
INNER JOIN dbo.PFFact cpff
ON cpf.CSID = cpff.CSID
AND cpf.PSID = cpff.PSID
INNER JOIN dbo.DateDim d
ON cpff.FDSID = d.DateKey
INNER JOIN dbo.CPosDim pd
ON cpff.PosSID = pd.PosSID
AND cpff.PosSID = pd.PosSID
INNER JOIN dbo.FDim fd
ON cpff.FlSID = fd.FlSID
AND cpff.FlSID = fd.FlSID
INNER JOIN dbo.FLJdim fljd
ON cpff.LJSID = fljd.LJSID
AND cpff.LJSID = fljd.LJSID
LEFT OUTER JOIN dbo.CrDim cd
ON cpff.CSID = cd.CrSID
WHERE cpff.IsPrActive = 1
AND d.Next7Days = 'Next 7 Days'
AND pd.PosCd = 'R8') arr
ON dep.EmpID = arr.EmpID
AND dep.FloatDepDate = arr.FloatDepDate
AND dep.Seq = arr.Seq + 1
May 30, 2012 at 3:44 pm
I got it, there is no issue...
an order by clause on the outer query reveals the problem. :angry:
so basically the query returns rows in the most efficient way and when certain columns were added/removed it changed the order in which rows were returned making it look like they vanished.
all the data is there it's just out of sequence.... Sorry for the trouble.
.... As someone once stated here on SSC "There is no default sort in SQL Server" I need to remember that!
May 30, 2012 at 3:53 pm
ok its late but this is what i see you have 2 inline tables an you are then innerjoining on those table therefore your result set would be that of a inner join. how about instead of using inline SQL create 2 temp tables with the result set you want and then do a left join beteen them hope this helps bit difficult without having a play with the data 😀
***The first step is always the hardest *******
May 30, 2012 at 3:54 pm
no trouble mate
***The first step is always the hardest *******
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply