Help with a complex JOIN - 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)

Help with a complex JOIN

Author

Topic

vqcheese
Starting Member

19 Posts

Posted - 2009-04-30 : 10:31:41

I have 2 tables hr2nj01(a) and hr2not01(b):

b has a field thats is the key called notesindex_i.
a has 2 fields called notesindex2_i and notesindex6_i and they are not keys.

The issue is when notes are written in the program they are put in table B as seperate records, note notesindex field just keeps incrementing for each record. IN table A one record might have 2 notes but they are in teh same record just in different fields. How can i join these so I get the correct data. Thanks.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-04-30 : 11:02:31

Please post some sample data and expected output so that someone can help.

vqcheese
Starting Member

19 Posts

Posted - 2009-04-30 : 11:13:12

EMPID_I INJURYNUMBER_I NOTESINDEX2_I NOTESINDEX6_I
9990214 919 302 303
------------------------above data is from table a------------
------------------------below data is from table b------------
NOTESINDEX_I EMPID_I NOTES32000_I
302 9990214 While pulling a se of boxes out of chamber 2
303 9990214 Pace himself and slow down.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-04-30 : 11:22:06

Ok..what is the expected output for the above data?

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-04-30 : 11:48:16

Not sure what exactly you need...maybe this is a start

declare @t table (employee_id int, injury_number int, notesindex2_i int, notesindex6_i int)
insert @t
select 9990214,919,302,303

declare @r table (notesindex_i int,empid_i int,notes32000_i varchar(200))
insert @r
select 302, 9990214, 'While pulling a set of boxes out of chamber 2' union all
select 303, 9990214, 'Pace himself and slow down'

select t.employee_id,
t.injury_number,
max(case when t.notesindex2_i = r.notesindex_i then t.notesindex2_i else '' end),
max(case when t.notesindex2_i = r.notesindex_i then r.notes32000_i else '' end),
max(case when t.notesindex6_i = r.notesindex_i then t.notesindex6_i else '' end),
max(case when t.notesindex6_i = r.notesindex_i then r.notes32000_i else '' end)
from @t t inner join @r r on t.employee_id = r.empid_i
group by t.employee_id,t.injury_number

Result
------
9990214 919 302 While pulling a set of boxes out of chamber 2 303 Pace himself and slow down

vqcheese
Starting Member

19 Posts

Posted - 2009-04-30 : 11:51:39

Something like this:
bascially combine those to data sets. soit will show employee 9990214 where he has those to index fields (302,303) aand will show his to notes fields all in 1 line.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-04-30 : 13:25:00

Ok..SO its whatever I gave...did that work?

vqcheese
Starting Member

19 Posts

Posted - 2009-04-30 : 14:10:55

Yeah it did, but i guess i was looking for something a little more simplified.

whitefang
Enterprise-Level Plonker Who's Not Wrong

272 Posts

Posted - 2009-04-30 : 14:18:35

SELECT a.EMPID_I, a.INJURYNUMBER_I, a.NOTESINDEX2_I, a.NOTESINDEX6_I, b.NOTES32000_I, b.NOTESINDEX FROM hr2nj01 a INNER JOIN hr2not01 b ON a.EMPID_I = b.EMPID_I

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-04-30 : 14:34:17

quote:
Originally posted by whitefang

SELECT a.EMPID_I, a.INJURYNUMBER_I, a.NOTESINDEX2_I, a.NOTESINDEX6_I, b.NOTES32000_I, b.NOTESINDEX FROM hr2nj01 a INNER JOIN hr2not01 b ON a.EMPID_I = b.EMPID_I

I dont think this will give OP the expected o/p. He wants everything in a single row.

vqcheese
Starting Member

19 Posts

Posted - 2009-05-01 : 14:03:28

I got this working, thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources