sql server programming: can this be one statement -- sql server programming

Re: can this be one statement

rodchar
5/25/2007 5:51:01 PM

yes, that's correct.

[quoted text, click to view]

Re: can this be one statement

Dan Guzman
5/25/2007 7:55:37 PM

[quoted text, click to view]

It depends what you mean by uncomplicated. The method below uses derived
tables:

CREATE TABLE dbo.Table1
(
empId int NOT NULL
CONSTRAINT PK_Table1 PRIMARY KEY,
fname varchar(30) NOT NULL,
lname varchar(30) NOT NULL
)
GO

INSERT INTO dbo.Table1
SELECT 1,'john','smith'
GO

CREATE TABLE dbo.Table2
(
empId int NOT NULL,
effective_date datetime NOT NULL,
salary money NOT NULL,
CONSTRAINT PK_Table2 PRIMARY KEY
(
empId,
effective_date
)
)
GO

INSERT INTO dbo.Table2
SELECT 1, '20050101', $25
UNION ALL SELECT 1, '20060101', $50
GO

CREATE TABLE dbo.Table3
(
empId int NOT NULL,
start_date datetime NOT NULL,
end_date datetime NULL,
jobPosition varchar(30) NOT NULL,
CONSTRAINT PK_Table3 PRIMARY KEY
(
empId,
start_date
)
)

INSERT INTO dbo.Table3
SELECT 1, '20050101', '20050601', 'director'
UNION ALL SELECT 1, '20050602', '20051231', 'executive'
UNION ALL SELECT 1, '20060101', NULL, 'ceo'
GO

SELECT
t1.empId,
t1.fname + ' ' + t1.lname AS FullName,
t2.CurrentSalary,
t3.CurrentPosition
FROM dbo.Table1 AS t1
JOIN (SELECT empId, salary AS CurrentSalary
FROM dbo.Table2 AS t2
WHERE effective_date = (
SELECT MAX(effective_date)
FROM dbo.Table2 AS latest_t2
WHERE latest_t2.empId = t2.empId)
) AS t2 ON t2.empId = t1.empId
JOIN (SELECT empId, jobPosition AS CurrentPosition
FROM dbo.Table3 AS t3
WHERE start_date = (
SELECT MAX(start_date)
FROM dbo.Table3 AS latest_t3
WHERE latest_t3.empId = t3.empId)
) AS t3 ON t3.empId = t1.empId

--
Hope this helps.

Dan Guzman
SQL Server MVP

[quoted text, click to view]

Re: can this be one statement

Mike Walsh
5/25/2007 8:35:06 PM

Yes..

SELECT T1.empID, T1.fname + ' ' + T1.lname as FullName, t2.salary,
t2.jobPosition
FROM Table1 AS T1
INNER JOIN Table2 AS T2
ON T1.empID = T2.empID

[quoted text, click to view]

Re: can this be one statement

Mike Walsh
5/25/2007 8:38:53 PM

Sorry I forgot Table3 when looking through your example. Same concept just
introduce another table
to the join.

SELECT T1.empID, T1.fname + ' ' + T1.lname as FullName, t2.salary,
t3.jobPosition
FROM Table1 AS T1
INNER JOIN Table2 AS T2
ON T1.empID = T2.empID
INNER JOIN Table3 AS T3
ON T1.EmpID = T3.empID

[quoted text, click to view]

Re: can this be one statement

Mike Walsh
5/25/2007 8:46:32 PM

okay.. so I actually totally missed the empID field, on my quick read I saw
it as part of a date and presumed we were talking about different employees.

For this report you actually just want the current salary and the current
position I presume?

Re: can this be one statement

Dan Guzman
5/25/2007 9:01:48 PM

[quoted text, click to view]

I loaded 1M, 2M and 3M rows into Table1, Table2 and Table3 respectively
using the script below. When I examined the execution plans of both queries
in a single batch, mine showed a relative cost of 43% vs. 57% for yours.
However, when I removed the extraneous GROUP BY from your query, the costs
and plans were identical. I guess the optimizer did it's job ;-)

Since rodchar didn't provide the actual schema (including indexes), we can
only guess what performance might be the the actual environment.

TRUNCATE TABLE dbo.Table1
TRUNCATE TABLE dbo.Table2
TRUNCATE TABLE dbo.Table3

INSERT INTO dbo.Table1
SELECT Nums.ID, 'john', 'smith'
FROM
(
SELECT Num1.Num + 1 + (Num2.Num * 10) + (Num3.Num * 100) +
(Num4.Num * 1000) + (Num5.Num * 10000) + (Num6.Num * 100000) AS ID
FROM (
SELECT 0 AS Num UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL
SELECT 3
UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9)
AS Num1
CROSS JOIN (
SELECT 0 AS Num UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL
SELECT 3
UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9)
AS Num2
CROSS JOIN (
SELECT 0 AS Num UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL
SELECT 3
UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9)
AS Num3
CROSS JOIN (
SELECT 0 AS Num UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL
SELECT 3
UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9)
AS Num4
CROSS JOIN (
SELECT 0 AS Num UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL
SELECT 3
UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9)
AS Num5
CROSS JOIN (
SELECT 0 AS Num UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL
SELECT 3
UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9)
AS Num6
) AS Nums

INSERT INTO dbo.Table2
SELECT empId, '20050101', $25
FROM dbo.Table1
UNION ALL
SELECT empId, '20060101', $50
FROM dbo.Table1

INSERT INTO dbo.Table3
SELECT empId, '20050101', '20050601', 'director' FROM dbo.Table1
UNION ALL SELECT empId, '20050602', '20051231', 'executive' FROM dbo.Table1
UNION ALL SELECT empId, '20060101', NULL, 'ceo' FROM dbo.Table1

UPDATE STATISTICS dbo.Table1
UPDATE STATISTICS dbo.Table2
UPDATE STATISTICS dbo.Table3
GO

--
Hope this helps.

Dan Guzman
SQL Server MVP

[quoted text, click to view]

Re: can this be one statement

Mike Walsh
5/25/2007 9:18:18 PM

alright so something like this should work for you:

select t1.emp_ID, t1.fname + ' ' + t1.last_name, tt2.salary ,
tt3.jobposition

from table1 as t1

inner join table2 tt2

on t1.emp_id = tt2.emp_id

and tt2.[date] = (select max(table2.[date]) from Table2

where table2.emp_Id = tt2.emp_id

group by table2.emp_id)

inner join table3 tt3

on t1.emp_id = tt3.emp_id

and tt3.start_date = (select max(table3.start_date) from table3

where table3.emp_id = tt3.emp_id)

Not as simple as what I first posted but that was because I had a simple
lack of understanding of your issue :)

[quoted text, click to view]

Re: can this be one statement

Mike Walsh
5/25/2007 9:23:28 PM

Or Dan's solution also works :)

They should both give you what you are looking for. I think Dan's is
prettier with using the derived table rather than using correlated
subqueries as I did in the join's ON clause. I just ran Dan's create table
scripts and compared the two. They both gave the same results with close
performance. The one I posted performed a bit better but this is not a real
world test with such small tables and I am not sure which would perform
better for a large table.

[quoted text, click to view]

Re: can this be one statement

Mike Walsh
5/25/2007 9:30:38 PM

[quoted text, click to view]
perform better? I would actually though Dan's would have from past
experience with complex join conditions (like on involving an = comparison
to a subquery).

Dan if you have a few moments could you compare them and help me wrap my
head around them? I am going to look at the query plans in more detail when
I have more time later but any thoughts help :)

Here is my version which should work with your DDL, Dan.

select t1.empID, t1.fname + ' ' + t1.lname, tt2.salary , tt3.jobposition

from table1 as t1

inner join table2 tt2

on t1.empid = tt2.empid

and tt2.effective_date = (select max(table2.effective_date) from Table2

where table2.empId = tt2.empid

group by table2.empid)

inner join table3 tt3

on t1.empid = tt3.empid

and tt3.start_date = (select max(table3.start_date) from table3

where table3.empid = tt3.empid)

[quoted text, click to view]

Re: can this be one statement

Mike Walsh
5/25/2007 10:10:53 PM

thanks. That was strange. I was getting 43 for mine and 57 for yours.
(without inserting the higher sample data). I took out the group by you
pointed out :) (I was going down a different path first and left that) and
then each query was good at 50%..

But on your machine with my oops group by your plan had the 43 and mine had
the 57 (percent of total relative cost) mirror plans. I guess my machine
favors me <eg>

Thanks for your time.
[quoted text, click to view]

Re: can this be one statement

Mr Tea
5/26/2007 3:08:26 PM

Heres another variant for SQL 2k5 but quickly falls behind the others in
terms of performance with over 100 employees.

SELECT
t1.empId,
t1.fname + ' ' + t1.lname AS FullName,
t2.CurrentSalary,
t3.CurrentPosition
FROM dbo.Table1 AS t1
OUTER APPLY (
SELECT Top 1 salary AS CurrentSalary
FROM dbo.Table2 AS it2
WHERE t1.empid=it2.empid
ORDER BY effective_date DESC
) t2
OUTER APPLY (
SELECT Top 1 jobPosition AS CurrentPosition
FROM dbo.Table3 AS it3
WHERE t1.empid=it3.empid
ORDER BY start_date DESC
) t3
Mr Tea

[quoted text, click to view]

RE: can this be one statement

rodchar
5/29/2007 8:09:01 AM

Thank you everyone for the great feedback. I appreciate very much.
Rod.

[quoted text, click to view]

Re: can this be one statement

rodchar
8/2/2007 12:46:12 PM

Still there anyone? Bueler?

Hey I have a new question regarding this old post if I may. What if Table2
and Table3 had some null records for a particular employee and I list all of
those employees only.

I tried appending "WHERE Salary = Null" to the encompassing SELECT statement
but it didn't work.

any ideas?

thanks,
rodchar

[quoted text, click to view]

Re: can this be one statement

rodchar
8/2/2007 1:28:09 PM

Thank you for the help.

[quoted text, click to view]

Re: can this be one statement

Aaron Bertrand [SQL Server MVP]
8/2/2007 3:52:43 PM

[quoted text, click to view]

You can't compare NULL using equality. Since NULL is, by definition,
"unknown" then it is impossible to say it whether is equal to or, more
importantly, NOT equal to something.

Perhaps you meant

WHERE Salary IS NULL
or
WHERE Salary IS NOT NULL

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server programming group.