Resolving 2 Keys in one SELECT

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)

Resolving 2 Keys in one SELECT

Author

Topic

VentureFree
Starting Member

19 Posts

Posted - 2009-07-03 : 07:08:03

I have a table that maps specific dates with a key and another table that has 2 separate columns containing such a key. I know this is probably a really trivial problem, but I can't seem to figure out how to select the date directly for both with a single join. Here's a trivial example of the tables and data that I'm working with:

CREATE TABLE [DATE_FACT](
	[NOM_DATE] [int] NOT NULL,
	[STD_DATE] [datetime] NOT NULL
)

INSERT INTO DATE_FACT (NOM_DATE, STD_DATE) VALUES (10, '2009-01-01')
INSERT INTO DATE_FACT (NOM_DATE, STD_DATE) VALUES (20, '2009-01-02')
INSERT INTO DATE_FACT (NOM_DATE, STD_DATE) VALUES (30, '2009-01-03')

CREATE TABLE [MY_TABLE](
	[MY_TABLE_KEY] [int] NOT NULL,
	[START_DATE] [int] NOT NULL,
	[END_DATE] [int] NOT NULL
)

INSERT INTO MY_TABLE (MY_TABLE_KEY, START_DATE, END_DATE) VALUES (1, 10, 20)
INSERT INTO MY_TABLE (MY_TABLE_KEY, START_DATE, END_DATE) VALUES (2, 20, 30)

Basically I'm trying to create a single select statement that gets the actual start and end dates from MY_TABLE rather than the nominal dates which is what START_DATE and END_DATE are stored as. I know how to get one or the other, but how do I get both? Here's how I'm getting just one:

SELECT MY_TABLE.MY_TABLE_KEY, DATE_FACT.STD_DATE
FROM MY_TABLE INNER JOIN DATE_FACT ON MY_TABLE.START_DATE = DATE_FACT.NOM_DATE
-- This returns the following:
-- 1, '2009-01-01 00:00:00.000'
-- 2, '2009-01-02 00:00:00.000'

How do I modify the select statement so that it can return the date for both START_DATE and END_DATE, instead of just one? NOTE: I already tried the following, but it went REALLY REALLY slow (it won't in this example, but with the real data it does):

SELECT MY_TABLE.MY_TABLE_KEY,
(SELECT STD_DATE FROM DATE_FACT WHERE NOM_DATE = MY_TABLE.START_DATE) AS STD_START_DATE,
(SELECT STD_DATE FROM DATE_FACT WHERE NOM_DATE = MY_TABLE.END_DATE) AS STD_END_DATE
FROM MY_TABLE

Surely there's a way to join the tables so that both keys can return a date without taking FOREVER to run. I know this has to be really simple, but I'm stuck in a mental cul-de-sac and can't seem to figure it out myself.

Thanks for any help.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-03 : 08:36:28

Try

SELECT MY_TABLE.MY_TABLE_KEY,t1.START_DATE,t2.END_DATE FROM MY_TABLE
INNER JOIN
(
SELECT STD_DATE FROM DATE_FACT
) as t1 on t1.NOM_DATE = MY_TABLE.START_DATE
INNER JOIN
(
SELECT STD_DATE FROM DATE_FACT
) as t2 on t2.NOM_DATE = MY_TABLE.END_DATE

Madhivanan

Failing to plan is Planning to fail

VentureFree
Starting Member

19 Posts

Posted - 2009-07-03 : 08:52:11

Thanks. That was close enough to work with a couple minor changes. And it's a whole heck of a lot faster than the attempt that I made. Here's the final working code in case anyone else needs it:

SELECT MY_TABLE.MY_TABLE_KEY,t1.STD_DATE AS START_DATE,t2.STD_DATE AS END_DATE FROM MY_TABLE
INNER JOIN
(
SELECT NOM_DATE, STD_DATE FROM DATE_FACT
) as t1 on t1.NOM_DATE = MY_TABLE.START_DATE
INNER JOIN
(
SELECT NOM_DATE, STD_DATE FROM DATE_FACT
) as t2 on t2.NOM_DATE = MY_TABLE.END_DATE

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-03 : 08:57:07

quote:
Originally posted by VentureFree

Thanks. That was close enough to work with a couple minor changes. And it's a whole heck of a lot faster than the attempt that I made. Here's the final working code in case anyone else needs it:
SELECT MY_TABLE.MY_TABLE_KEY,t1.STD_DATE AS START_DATE,t2.STD_DATE AS END_DATE FROM MY_TABLE
INNER JOIN
(
SELECT NOM_DATE, STD_DATE FROM DATE_FACT
) as t1 on t1.NOM_DATE = MY_TABLE.START_DATE
INNER JOIN
(
SELECT NOM_DATE, STD_DATE FROM DATE_FACT
) as t2 on t2.NOM_DATE = MY_TABLE.END_DATE

You are welcome

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources