Inner join problem - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Inner join problem

Author

Topic

birre69
Starting Member

5 Posts

Posted - 2007-04-05 : 03:59:33

Hello,

I have to get some data out of 2 tables.

Replication of the tables:

Werknemer_090:
NrWg_090 WnNr_090 Naam_090 Voornaam_090
10 1 test test
10 2 test2 test2
9 5 test3 test3
WnTewerkstelling_092:
NrWg_092 WnNr_092 VolgNr_092 Statuut_092
10 1 1 b
10 1 2 b
10 2 1 a
9 5 1 b

So I need to get Naam_090, Voornaam_090, Statuut_092. The problem is that in the table WnTewerkstelling_092, the pk = NrWg_092, WnNr_092, VolgNr_092. NrWg_092 and WnNr_092 can contain duplicate values. And I only want the employees (Werknemers) once with there statuut (statuut_092).
Someone can help me?

sorry of the positioning

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-05 : 04:12:20

[code]-- prepare sample data
declare @Werknemer_090 table
(
NrWg_090 int,
WnNr_090 int,
Naam_090 varchar(50),
Voornaam_090 varchar(50)
)

declare @WnTewerkstelling_092 table
(
NrWg_092 int,
WnNr_092 int,
VolgNr_092 int,
Statuut_092 varchar(1)
)

insert @Werknemer_090
select 10, 1, 'test', 'test' union all
select 10, 2, 'test2', 'test2' union all
select 9, 5, 'test3', 'test3'

insert @WnTewerkstelling_092
select 10, 1, 1, 'b' union all
select 10, 1, 2, 'b' union all
select 10, 2, 1, 'a' union all
select 9, 5, 1, 'b'

-- final query
select t1.Naam_090, t1.Voornaam_090, t2.Statuut_092
from @Werknemer_090 t1
join (select distinct NrWg_092, WnNr_092, Statuut_092 from @WnTewerkstelling_092) t2
on t1.NrWg_090 = t2.NrWg_092 and t1.WnNr_090 = t2.WnNr_092[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

birre69
Starting Member

5 Posts

Posted - 2007-04-05 : 04:31:17

quote:

Originally posted by harsh_athalye

-- prepare sample data
declare @Werknemer_090 table
(
	NrWg_090 int,
	WnNr_090 int,
	Naam_090 varchar(50),
	Voornaam_090 varchar(50)
)

declare @WnTewerkstelling_092 table
(
	NrWg_092 int,
	WnNr_092 int,
	VolgNr_092 int,
	Statuut_092 varchar(1)
)

insert @Werknemer_090
select 10, 1, 'test', 'test' union all
select 10, 2, 'test2', 'test2' union all
select 9, 5, 'test3', 'test3'

insert @WnTewerkstelling_092
select 10, 1, 1, 'b' union all
select 10, 1, 2, 'b' union all
select 10, 2, 1, 'a' union all
select 9, 5, 1, 'b'

-- final query
select t1.Naam_090, t1.Voornaam_090, t2.Statuut_092
from @Werknemer_090 t1 
join (select distinct NrWg_092, WnNr_092, Statuut_092 from @WnTewerkstelling_092) t2
on t1.NrWg_090 = t2.NrWg_092 and t1.WnNr_090 = t2.WnNr_092

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

This works fine, ty
but there is still a litle prob.

NrWn Naam Voornaam Statuut
2 TEST WERKNEMER SOFIE
2 TEST WERKNEMER SOFIE 0

Sometimes ther are double rows because of 'statuut' that is empty in a row, but not in the other of the same employee.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-05 : 04:34:54

[code]-- prepare sample data
declare @Werknemer_090 table
(
NrWg_090 int,
WnNr_090 int,
Naam_090 varchar(50),
Voornaam_090 varchar(50)
)

declare @WnTewerkstelling_092 table
(
NrWg_092 int,
WnNr_092 int,
VolgNr_092 int,
Statuut_092 varchar(1)
)

insert @Werknemer_090
select 10, 1, 'test', 'test' union all
select 10, 2, 'test2', 'test2' union all
select 9, 5, 'test3', 'test3'

insert @WnTewerkstelling_092
select 10, 1, 1, 'b' union all
select 10, 1, 2, '' union all
select 10, 2, 1, 'a' union all
select 9, 5, 1, 'b'

-- final query
select t1.Naam_090, t1.Voornaam_090, t2.Statuut_092
from @Werknemer_090 t1
join (select distinct NrWg_092, WnNr_092, Statuut_092 from @WnTewerkstelling_092 where Statuut_092 is not null and Statuut_092 <> '') t2
on t1.NrWg_090 = t2.NrWg_092 and t1.WnNr_090 = t2.WnNr_092[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

birre69
Starting Member

5 Posts

Posted - 2007-04-05 : 04:45:42

ok,

ty verry much

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources