Problem With Left Join Table - 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

General SQL Server Forums

New to SQL Server Programming

Problem With Left Join Table

Author

Topic

jaya
Starting Member

2 Posts

Posted - 2008-05-07 : 08:28:44

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-07 : 08:30:41

Will you be always certain that plb_penataran will have 2 records for each record of plb_siswa?

eralper
Yak Posting Veteran

66 Posts

Posted - 2008-05-07 : 09:58:04

Hello,
Actually you need to concatenate rows into one column.

I can suggest you the XML PATH() approach if you are using SQL Server 2005 or 2008.

There is an article on this topic titled [url]http://www.kodyaz.com/articles/concatenate-using-xml-path.aspx[/url]

Also you can use UDFs (user-defined functions) that use COALESCE function to concatemate row columns as one string value.
There is a sample at[url]http://www.kodyaz.com/article.aspx?ArticleID=29[/url] and a thread at [url]http://www.kodyaz.com/forums/thread/76.aspx[/url]

I hope these will be helpful for you

Eralper

-------------
Eralper
http://www.kodyaz.com

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-05-07 : 10:10:37

quote:
Originally posted by eralper

Actually you need to concatenate rows into one column.

How do you figure that?

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

eralper
Yak Posting Veteran

66 Posts

Posted - 2008-05-09 : 02:25:49

Hello,

Sorry I think I misunderstood the question :(

Here is a new t-sql script I guess it is what is desired, give it a try :)

select
NSS, NIP, NAMA, JENIS,
MAX(NAMA_PENATARAN1) NAMA_PENATARAN1,
MAX(NAMA_PENATARAN2) NAMA_PENATARAN2,
MAX(TAHUN1) TAHUN1,
MAX(TAHUN2) TAHUN2
from
(
select
NSS, NIP, NAMA, JENIS,
NAMA_PENATARAN1 = case when rownumber_for_person = 1 then NAMA_PENATARAN else null end,
NAMA_PENATARAN2 = case when rownumber_for_person = 2 then NAMA_PENATARAN else null end,
TAHUN1 = case when rownumber_for_person = 1 then TAHUN else null end,
TAHUN2 = case when rownumber_for_person = 2 then TAHUN else null end
from
(
select
DENSE_RANK() over (order by s.NSS, s.NIP, s.NAMA, s.JENIS) person_number,
ROW_NUMBER() over (partition by s.NSS, s.NIP, s.NAMA, s.JENIS order by tahun) rownumber_for_person,
s.NSS, s.NIP, s.NAMA, s.JENIS, p.NAMA_PENATARAN, p.TAHUN
from plb_siswa s
inner join plb_penataran p on s.NSS = p.NSS and s.NIP = p.NIP
) t
) tt
group by NSS, NIP, NAMA, JENIS

Eralper
http://www.kodyaz.com

-------------
Eralper
http://www.kodyaz.com

jaya
Starting Member

2 Posts

Posted - 2008-05-09 : 02:47:26

quote:
Originally posted by visakh16

Will you be always certain that plb_penataran will have 2 records for each record of plb_siswa?

Plb_penataran is not certain have 2 record, it could be have more than two record with the same person, it could be have 1, 2 or 3 record of plb_siswa. I'm using sqlserver 2000 for these database. btw, thank's for your response guys.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-05-09 : 03:41:41

quote:
Originally posted by jaya

quote:
Originally posted by visakh16

Will you be always certain that plb_penataran will have 2 records for each record of plb_siswa?

Plb_penataran is not certain have 2 record, it could be have more than two record with the same person, it could be have 1, 2 or 3 record of plb_siswa. I'm using sqlserver 2000 for these database. btw, thank's for your response guys.

Does that mean, definitely no more than 3 records? If so, it's much easier...

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-05-09 : 09:25:12

In SQL 2000, you have to manage without the ROW_NUMBER type functions, and one common way is like this. As you can see, it is similar eralper's suggestion, but uses a (sadly slower) row numbering 'trick'.

-- Structure and Data
declare @plb_siswa table (NSS int, NIP int, NAMA varchar(10), JENIS varchar(10))
insert @plb_siswa
          select 28101712, 992002, 'ROSIAH', 'GURU'
union all select 1, 2, 'a', 'b'
union all select 3, 4, 'd', 'e'

declare @plb_penataran table (NSS int, NIP int, NAMA_PENATARAN varchar(30), TAHUN int)
insert @plb_penataran
          select 28101712, 992002, 'KETRAMPILAN', 2000
union all select 28101712, 992002, 'PENYULUH BAHASA INDONESIA', 2001
union all select 1, 2, 'c', 3
union all select 3, 4, 'f', 5
union all select 3, 4, 'g', 6
union all select 3, 4, 'h', 7

-- Calculation
select a.NSS, a.NIP, a.NAMA, a.JENIS,
    max(case when RowNumber = 1 then NAMA_PENATARAN end) as PENATARAN1,
    max(case when RowNumber = 2 then NAMA_PENATARAN end) as PENATARAN2,
    max(case when RowNumber = 3 then NAMA_PENATARAN end) as PENATARAN3,
    max(case when RowNumber = 1 then TAHUN end) as TAHUN1,
    max(case when RowNumber = 2 then TAHUN end) as TAHUN2,
    max(case when RowNumber = 3 then TAHUN end) as TAHUN3
from @plb_siswa a inner join 
    (select *, (select count(*) from @plb_penataran where NSS = a.NSS and
                NIP = a.NIP and NAMA_PENATARAN <= a.NAMA_PENATARAN) as RowNumber
    from @plb_penataran a) b on a.NSS = b.NSS and a.NIP = b.NIP
group by a.NSS, a.NIP, a.NAMA, a.JENIS

/* Results
NSS         NIP         NAMA       JENIS      PENATARAN1      PENATARAN2                     PENATARAN3     TAHUN1      TAHUN2      TAHUN3
----------- ----------- ---------- ---------- --------------- ------------------------------ -------------- ----------- ----------- -----------
1           2           a          b          c               NULL                           NULL           3           NULL        NULL
3           4           d          e          f               g                              h              5           6           7
28101712    992002      ROSIAH     GURU       KETRAMPILAN     PENYULUH BAHASA INDONESIA      NULL           2000        2001        NULL
*/

If this isn't what you need, post more sample data as an example.

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources