help with multiple tables using outer joins

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

help with multiple tables using outer joins

Author

Topic

jctech
Starting Member

28 Posts

Posted - 2012-06-21 : 16:59:58

Hello,
I am trying to attach another column to a query and am receiving "NULL"s for all the results. The line in bold is what I added which is resulting as null. I do not understand why.
Here is the query...

select A.employid,A.chekdate,A.untstopy,B.untstopy,C.untstopy
from A
left outer join B on (A.employid=B.employid and A.chekdate=B.chekdate)
left outer join C on (A.employid=C.employid and A.chekdate=C.chekdate)
order by A.employid, A.chekdate

A,B,C are views I created. The UNTSTOPY column are the number of hours worked. A is showing regular time. B is showing overtime. C is showing PTO. A,B,and C are linked by the employid's and chekdate.
Below is an example of the results:

employid chekdate untstopy untstopy untstopy
096058 2011-06-10 27.75000 1.00000 NULL
096058 2011-06-24 80.00000 2.24000 NULL
096058 2011-07-08 77.88000 3.23000 NULL
096058 2011-07-22 80.00000 3.94000 NULL
096058 2011-08-05 64.00000 3.96000 NULL
096058 2011-08-19 80.13000 2.79000 NULL
096058 2011-09-02 40.00000 1.51000 NULL

I am pretty new to SQL so I hope I am making sense...thank you.

-Jae

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2012-06-21 : 17:42:57

Looks like you have no rows in C for any of those particular employid and chekdate values. OR you have rows but the unstopy column in C is null.

try:
select * from C where employid = '096058' and chekdate = '2011-06-10'

Another possibility is that your employid values and/or your chekdate values are formatted differently in C than in A and B so they aren't correlating.

Be One with the Optimizer
TG

jctech
Starting Member

28 Posts

Posted - 2012-06-21 : 17:58:51

When I run a query "select * from C" I get several hundred results with untstopy populated.

It is just on this multi-table query that shows the untstopy column from C populated with NULL for every and all employid.

Is the 2nd left outer join combining with the 1st left outter join...or are they independant of each other?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-21 : 18:16:17

i think its problem with date values been not matching between A and C

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jctech
Starting Member

28 Posts

Posted - 2012-06-21 : 18:27:46

Tables A,B,C are created views all from the same master table.
The difference between the three views are filters in the paycode. One view is for regular time, another for overtime, and PTO time.
The paydates are the same....shared.

I'm just trying to get the different paycode hours to show in one query result.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2012-06-22 : 15:46:00

>>Tables A,B,C are created views all from the same master table.
>>I'm just trying to get the different paycode hours to show in one query result.

Then why don't you just query the master table then?

>>When I run a query "select * from C"...
My point was to run it WHERE employid = '096058' and chekdate = '2011-06-10'
That is what you are correlating on so I expect there is either no row for that particular employee and date or that row has a NULL untstopy value.

Be One with the Optimizer
TG

jctech
Starting Member

28 Posts

Posted - 2012-06-22 : 17:13:39

Here is what the original table looks like:

PAYROLCD UNTSTOPY
H-RT 80
H-OT 0.92
H-RT 50.94
S-OT 16
S-PTO 24
S-PTO 16
S-PTO 16

Those are the two main columns...with employid, chekdate, and several other columns.
I created views(A,B,C) to have the unit hours from UNTSTOPY filtered.
A is showing regular time. B is showing overtime. C is showing PTO.

I don't know of a way to query the master table where the same column would show 3 times....each filtered differently.
Would you know how to go about that...?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-22 : 22:48:47

yep. thats a typical cross tab scenario. you can use either of below two method

1.Pivot (SQL 2005 and above)


SELECT *
FROM (SELECT employid,chekdate,UNTSTOPY, STUFF(PAYROLCD,1,CHARINDEX('-',PAYROLCD),'') AS Category FROM table) t
PIVOT(SUM(UNTSTOPY) FOR Category  IN ([RT],[OT],[PTO]))p

2. Any version solution


SELECT employid,chekdate,
SUM(CASE WHEN Category = 'RT' THEN UNTSTOPY END) AS [RT],
SUM(CASE WHEN Category = 'OT' THEN UNTSTOPY END) AS [OT],
SUM(CASE WHEN Category = 'PTO' THEN UNTSTOPY END) AS [PTO]
FROM (SELECT employid,chekdate,UNTSTOPY, STUFF(PAYROLCD,1,CHARINDEX('-',PAYROLCD),'') AS Category FROM table) t
GROUP BY employid,chekdate

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jctech
Starting Member

28 Posts

Posted - 2012-06-25 : 14:39:44

The two results are different but it looks like the pivot works. Thank you visakh16!

I've never heard of pivot queries...but I have seen some Case/End queries.
On a side note, where could I go to read/learn more about pivot queries and is there a good online course that you could recommend for SQL?

Thanks again~!

-Jae

jctech
Starting Member

28 Posts

Posted - 2012-06-25 : 16:12:49

By the way, how would I go about removing the results for all three RT,OT,and PTO showing "NULL"?

I tried "where Category <> NULL" but I receive 0 results when I input that...thanks again.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2012-06-25 : 16:54:08

You can't compare values to NULL - ie: (=NULL !=NULL <>NULL) None of those will work.
You need to use:
WHERE <value> IS NOT NULL
or
WHERE <value> IS NULL

if you need more help let us know which version you are using (pivot or any version).

Be One with the Optimizer
TG

stonebreaker
Yak Posting Veteran

92 Posts

Posted - 2012-06-26 : 12:19:59

Are the dates you are comparing actually DATE data types, or are they DATETIME or TIMESTAMP? If the latter, you would probably want to just use the date part of the DATETIME value.

Stonebreaker
The greatest obstacle to discovery is not ignorance - it is the illusion of knowledge.
-Daniel Boorstin

jctech
Starting Member

28 Posts

Posted - 2012-06-29 : 15:26:19

For the pivot above, how and where would I insert "WHERE RT, OT, PTO IS NOT NULL"?
I would want to exclude lines where all three RT, OT, PTO equals NULL...

SELECT *
FROM (SELECT employid,chekdate,UNTSTOPY, STUFF(PAYROLCD,1,CHARINDEX('-',PAYROLCD),'') AS Category FROM table) t
PIVOT(SUM(UNTSTOPY) FOR Category IN ([RT],[OT],[PTO]))p

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2012-06-29 : 17:09:48

see if this works:


select *
from	(
	SELECT employid,chekdate,UNTSTOPY, Category
	FROM (
		SELECT employid,chekdate,UNTSTOPY, STUFF(PAYROLCD,1,CHARINDEX('-',PAYROLCD),'') AS Category 
		FROM [table]
		) t
	where	Category in ('RT','OT','PTO')
	) d
PIVOT(SUM(UNTSTOPY) FOR Category IN ([RT],[OT],[PTO]))p

Be One with the Optimizer
TG

jctech
Starting Member

28 Posts

Posted - 2012-06-29 : 18:04:19

Wow, I think that did it. What do those "t" and "d" stand for and what are those expressions called?

Thanks a bunch TG!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2012-06-29 : 20:46:03

The statements inside parentheses are derived tables. The d and t are table aliases for those derived tables.

Be One with the Optimizer
TG

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-29 : 22:38:59

quote:
Originally posted by TG

You can't compare values to NULL - ie: (=NULL !=NULL <>NULL) None of those will work.
You need to use:
WHERE <value> IS NOT NULL
or
WHERE <value> IS NULL

if you need more help let us know which version you are using (pivot or any version).

Be One with the Optimizer
TG

need to add "under default conditions"

ANSI NULL settings can change behaviour

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2012-07-01 : 12:27:57

quote:
Originally posted by visakh16

quote:
Originally posted by TG

You can't compare values to NULL - ie: (=NULL !=NULL <>NULL) None of those will work.
You need to use:
WHERE <value> IS NOT NULL
or
WHERE <value> IS NULL

if you need more help let us know which version you are using (pivot or any version).

Be One with the Optimizer
TG

need to add "under default conditions"

ANSI NULL settings can change behaviour

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

I intentionally did not want to encourage setting ansi_nulls off

I probably should have said "shouldn't" instead of "can't".

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources