join tables with no primary key

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)

join tables with no primary key

Author

Topic

umutos
Starting Member

12 Posts

Posted - 2010-05-20 : 08:54:13

Hello!

I have 2 Tables I want to join:

Table1:


date_a	date_b	age	name
01.01.2000	02.01.2013	23	Nick
01.01.1999	02.01.2011	42	Mark
02.03.2004	02.01.2013	12	John
01.01.2000	01.01.2001	32	Lisa
01.04.2002	02.03.2004	12	Mary
01.01.2000	02.01.2013	11	Gina

date_a is smalldatetime
date_b is smalldatetime

Table2:


date_a	days	office
01.01.2000	4750	A
01.01.1999	4384	B
02.03.2004	3228	C
01.01.2000	366	D
01.04.2002	701	E

date_a is smalldatetime
days is int and office is real(not here but in my sample

)

the colum "days" has the following relationship:

date_a+days=date_b

after the joining i want to get the following table:


date_a	date_b	age	name	office
01.01.2000	02.01.2013	23	Nick	A
01.01.1999	02.01.2011	42	Mark	B
02.03.2004	02.01.2013	12	John	C
01.01.2000	01.01.2001	32	Lisa	D
01.04.2002	02.03.2004	12	Mary	E
01.01.2000	02.01.2013	11	Gina	A

so I want to match this 2 tables by using 2 colums as "primary key".
I tried the folowing:


select d1.date_a,d1.date_b, d2.office from
table_1 d1
join table_2 d2 on d1.date_a=d2.date_a AND dateadd(day,d2.days,d2.date_a)=d1.date_a

WIth this code SQL is joining succesfully but the colum d2.office (or any other colum of table_2 after joining with table_01) have just the values 'NULL' (i have a german version, maybe in an english version it is "ZERO").
Then I tried the following codes:


select d1.date_a,d1.date_b, d2.office from
table_1 d1,table_2 d2
WHERE d1.date_a=d2.date_a AND dateadd(day,d2.days,d2.datue_a)=d1.date_a

same result.and also this one ( i tried to merge both colums to 1 primary key):


select d1.date_a,d1.date_b, d2.office from
table_1 d1
join table_2 d2 on cast(d1.date_a as char)+cast(d2.date_a as char)=cast(d2.date_a as char)+cast(dateadd(day,d2.days,d2.date_a) as char)

also the same result. (same result also when i convert into smalldatetime or datetime instead of char)

what does this "NULL" in every row mean? SQL tells me that joining was succesfully but i cant see the values from table_2.

thanks for help!

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-05-20 : 10:19:42

Shouldn't it be

select d1.date_a,d1.date_b, d2.office from
table_1 d1
join table_2 d2 on d1.date_a=d2.date_a AND dateadd(day,d2.days,d2.date_a)=d1.date_b

See the highlighted part in red.

umutos
Starting Member

12 Posts

Posted - 2010-05-26 : 07:49:10

thank you. problem solved!

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-05-26 : 11:08:45

Hmmm. Np

. But 6 days to change _a to _b ...just kidding

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources