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
 Old Forums
 CLOSED - General SQL Server
 Table join problems

Author  Topic 

rajmalempati
Starting Member

8 Posts
Posted - 2006-05-11 : 23:12:38
Hello Every One
I am trying to join below two tables to get right results

---------------------------------------------------
NF02---table
--------------------------------------------------

sccfno rsncd seqno fmtdbpostdt

1001 152 2 01/02/2006
1001 152 4 01/04/2006
1001 153 6 01/10/2006

1002 152 1 01/02/2006

1003 153 1 01/03/2006
1003 153 2 01/04/2006

-------------------------------------------------

NF03 -----Table
-------------------------------------------------

sccfno rsncd seqno fmtdbpostdt

1001 152 3 01/03/2006
1001 152 5 01/06/2006
1001 153 7 01/11/2006

1002 152 2 01/03/2006

1003 153 3 01/05/2006
1003 153 4 01/06/2006

--------------------------------------------------------

Can any one help to write sql query to join above two tables.
After joining above two tables,I would like to see the results following way
----------------------------------------------------------


sccfno rsncd seqno fmtdbpostdt sccfno rsncd seqno fmtdbpostdt

1001 152 2 01/02/2006 1001 152 3 01/03/2006
1001 152 4 01/04/2006 1001 152 5 01/06/2006
1001 153 6 01/10/2006 1001 153 7 01/11/2006

1002 152 1 01/02/2006 1002 152 2 01/03/2006

1003 153 1 01/03/2006 1003 153 3 01/05/2006
1003 153 2 01/04/2006 1003 153 4 01/06/2006

khtan
In (Som, Ni, Yak)

17689 Posts
Posted - 2006-05-11 : 23:26:48
[code]select *
from NF02 a inner join NF03 b
on a.sccfno = b.sccfno
and a.rsncd = b.rsncd
and a.seq_no = b.seq_no - 1[/code]

KH
Go to Top of Page

rajmalempati
Starting Member

8 Posts
Posted - 2006-05-12 : 07:19:31
Hi Kh

Thanks for your reply,But Your logic is not working for last two records,
------------------------
1003 153 1 01/03/2006
1003 153 2 01/04/2006
------------------------

1003 153 3 01/05/2006
1003 153 4 01/06/2006
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts
Posted - 2006-05-12 : 09:46:01
[code]select *
from NF02 a inner join NF03 b
on a.sccfno = b.sccfno
and a.rsncd = b.rsncd
and (select count(*) from NF02 x where x.sccfno = a.sccfno and x.rsncd = a.rsncd and x.seqno <= a.seqno) =
(select count(*) from NF03 x where x.sccfno = b.sccfno and x.rsncd = b.rsncd and x.seqno <= b.seqno)
order by a.sccfno, a.rsncd, a.seqno[/code]

KH
Go to Top of Page

rajmalempati
Starting Member

8 Posts
Posted - 2006-05-12 : 13:21:04
Hi KH
Thanks for helping me out. It’s working for 90% of records,
But I have a scenario like this in my tables, and then our logic is not working

Ex
-----------------------------------------
NF02 Table
-----------------------------------------
Sccfno Rsncd seqno date

1001 152 3 01/08/2006


-----------------------------------------
NF03 Table
-----------------------------------------
Sccfno rsncd seqno date

1001 152 2 01/06/2006
1001 152 4 01/10/2006


In the above scenario, my right match is below

1001 152 3 01/08/2006 1001 152 4 01/10/2006


I am using nf02 table to find match in NF03 table, and look for greater seqno or dates.

Your logic working perfect but if the above scenario .It’s taking first record of NF03 table instead of taking 2nd.
Can you help me out, how our query retrieves next highest seqno for above scenario?
Thanks
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts
Posted - 2006-05-12 : 20:02:07
just add a condition for NF03.seqno > NF02.seqno

KH
Go to Top of Page

rajmalempati
Starting Member

8 Posts
Posted - 2006-05-12 : 21:37:21
select *
from NF02 a inner join NF03 b
on a.sccfno = b.sccfno
and a.rsncd = b.rsncd
and (select count(*) from NF02 x where x.sccfno = a.sccfno and x.rsncd = a.rsncd and x.seqno <= a.seqno) =
(select count(*) from NF03 x where x.sccfno = b.sccfno and x.rsncd = b.rsncd and x.seqno <= b.seqno)
order by a.sccfno, a.rsncd, a.seqno

for above query where i have to add this condition?
Go to Top of Page

cmdr_skywalker
Posting Yak Master

159 Posts
Posted - 2006-05-12 : 21:48:31
I am not sure if the selection based on the first matching row of NF tables (x.sccfno = a.sccfno and x.rsncd = a.rsncd) or the lesser sequence number? Do you have a case where you have two records of NF02 (i.e. 1001 152 2 01/02/2006 and 1001 152 2 01/03/2006, in this case, what should be selected)?



May the Almighty God bless us all!
Go to Top of Page

rajmalempati
Starting Member

8 Posts
Posted - 2006-05-13 : 09:22:04

No I don't have that kind of scenario in my NF02 table.If you look at my nf02 table ,sequno will be changed for every record.
first 3 cloumns in the table are key fields in both tables(ie NF02,Nf03)

---------------------------------------------------
NF02---table
--------------------------------------------------

sccfno rsncd seqno fmtdbpostdt

1001 152 2 01/02/2006
1001 152 4 01/04/2006
1001 153 6 01/10/2006

1002 152 1 01/02/2006

1003 153 1 01/03/2006
1003 153 2 01/04/2006

1004 152 3 01/04/2006
-------------------------------------------------

NF03 -----Table
-------------------------------------------------

sccfno rsncd seqno fmtdbpostdt

1001 152 3 01/03/2006
1001 152 5 01/06/2006
1001 153 7 01/11/2006

1002 152 2 01/03/2006

1003 153 3 01/05/2006
1003 153 4 01/06/2006

1004 152 2 01/04/2006
1004 152 5 01/06/2006
Go to Top of Page

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts
Posted - 2006-05-15 : 11:17:36
Hi all,

This is horrendous , but here goes my stab...

I added one more set of test data (1005). rajmalempati - please let us know if that one doesn't behave as you'd expect (I took a bit of a guess).

--data
set dateformat mdy
declare @NF02 table (sccfno int, rsncd int, seqno int, fmtdbpostdt datetime)
insert @NF02
          select 1001, 152, 2, '01/02/2006'
union all select 1001, 152, 4, '01/04/2006'
union all select 1001, 153, 6, '01/10/2006'

union all select 1002, 152, 1, '01/02/2006'

union all select 1003, 153, 1, '01/03/2006'
union all select 1003, 153, 2, '01/04/2006'

union all select 1004, 152, 3, '01/04/2006'

union all select 1005, 152, 2, '01/04/2006'
union all select 1005, 152, 5, '01/07/2006'

declare @NF03 table (sccfno int, rsncd int, seqno int, fmtdbpostdt datetime)
insert @NF03
          select 1001, 152, 3, '01/03/2006'
union all select 1001, 152, 5, '01/06/2006'
union all select 1001, 153, 7, '01/11/2006'

union all select 1002, 152, 2, '01/03/2006'

union all select 1003, 153, 3, '01/05/2006'
union all select 1003, 153, 4, '01/06/2006'

union all select 1004, 152, 2, '01/04/2006'
union all select 1004, 152, 5, '01/06/2006'

union all select 1005, 152, 1, '01/03/2006'
union all select 1005, 152, 3, '01/05/2006'
union all select 1005, 152, 4, '01/07/2006'
union all select 1005, 152, 6, '01/07/2006'
union all select 1005, 152, 7, '01/08/2006'

--calculation
select d.sccfno, d.rsncd, d.seqno, d.fmtdbpostdt,
       e.sccfno, e.rsncd, e.seqno, e.fmtdbpostdt
from
    (
    select *, case when minrowno > rowno then minrowno else rowno end as adjustedrowno from (
    select	*,
        (select count(*) from @NF02 where sccfno = a.sccfno and seqno <= a.seqno) as rowno,
        (select count(*) + 1 from @NF03 where sccfno = a.sccfno and fmtdbpostdt <= a.fmtdbpostdt) as minrowno
    from @NF02 a) b
    ) d
inner join
    (
    select *, (select count(*) from @NF03 where sccfno = c.sccfno and seqno <= c.seqno) as rowno
    from @NF03 c
    ) e
on d.sccfno = e.sccfno and d.adjustedrowno = e.rowno

--results
sccfno      rsncd       seqno       fmtdbpostdt                                            sccfno      rsncd       seqno       fmtdbpostdt                                            
----------- ----------- ----------- ------------------------------------------------------ ----------- ----------- ----------- ------------------------------------------------------ 
1001        152         2           2006-01-02 00:00:00.000                                1001        152         3           2006-01-03 00:00:00.000
1001        152         4           2006-01-04 00:00:00.000                                1001        152         5           2006-01-06 00:00:00.000
1001        153         6           2006-01-10 00:00:00.000                                1001        153         7           2006-01-11 00:00:00.000
1002        152         1           2006-01-02 00:00:00.000                                1002        152         2           2006-01-03 00:00:00.000
1003        153         1           2006-01-03 00:00:00.000                                1003        153         3           2006-01-05 00:00:00.000
1003        153         2           2006-01-04 00:00:00.000                                1003        153         4           2006-01-06 00:00:00.000
1004        152         3           2006-01-04 00:00:00.000                                1004        152         5           2006-01-06 00:00:00.000
1005        152         2           2006-01-04 00:00:00.000                                1005        152         3           2006-01-05 00:00:00.000
1005        152         5           2006-01-07 00:00:00.000                                1005        152         7           2006-01-08 00:00:00.000



Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.
Go to Top of Page

rajmalempati
Starting Member

8 Posts
Posted - 2006-05-15 : 12:52:19
Hi RyanRandall
Thanks for your reply,It's working 95%
But when dates are equal,it's not working.see below example

---------------------------------------------------
NF02---table
--------------------------------------------------

sccfno rsncd seqno fmtdbpostdt

1003 153 1 01/03/2006
1003 153 2 01/06/2006

-------------------------------------------------

NF03 -----Table
-------------------------------------------------

sccfno rsncd seqno fmtdbpostdt

1003 153 3 01/05/2006
1003 153 4 01/06/2006

------------------------------------------------
For the above scenario,Our query bringing only first record from nf03 table.

I have to bring two records from nf03 table like below.

1003 153 1 01/03/2006 1003 153 3 01/05/2006
1003 153 2 01/06/2006 1003 153 4 01/06/2006
---------------------------------------------------------------------
Go to Top of Page

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts
Posted - 2006-05-15 : 13:28:59
Okay - I don't think I yet understand (fully) what it is you need to do then.

What should be your results if you have this (as you gave just then, but one extra record in NF03)...

---------------------------------------------------
NF02---table
--------------------------------------------------

sccfno rsncd seqno fmtdbpostdt

1003 153 1 01/03/2006
1003 153 2 01/06/2006

-------------------------------------------------

NF03 -----Table
------------------------------------------------- 

sccfno rsncd seqno fmtdbpostdt

1003 153 3 01/05/2006
1003 153 4 01/06/2006
1003 153 5 01/07/2006
Also, can you please confirm that everything was correct for the data and output of my first post (particularly for 1004)?

Thanks...

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.
Go to Top of Page

rajmalempati
Starting Member

8 Posts
Posted - 2006-05-15 : 18:29:17
Hi Ryan Randall

Thank you very much,Now it's working fine.you are really GENIUS.
I appreciate your help.
If possible can you pls explain logic of that query(step by step).you
can send me mail rajumalempati@gmail.com

Thanks
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts
Posted - 2006-05-16 : 02:21:04
That's Great ! Ryan

KH
Go to Top of Page

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts
Posted - 2006-05-16 : 05:31:46
Excellent! Glad it works rajmalempati.

I've tried my best to explain the steps in the code below. I've split out the derived tables (b, d and e) and explained what they are doing. Run it with 'Results in Text' to see the data and explanations together. I hope that helps. 

--data
set dateformat mdy
declare @NF02 table (sccfno int, rsncd int, seqno int, fmtdbpostdt datetime)
insert @NF02
          select 1001, 152, 2, '01/02/2006'
union all select 1001, 152, 4, '01/04/2006'
union all select 1001, 153, 6, '01/10/2006'

union all select 1002, 152, 1, '01/02/2006'

union all select 1003, 153, 1, '01/03/2006'
union all select 1003, 153, 2, '01/04/2006'

union all select 1004, 152, 3, '01/04/2006'

union all select 1005, 152, 2, '01/04/2006'
union all select 1005, 152, 5, '01/07/2006'

declare @NF03 table (sccfno int, rsncd int, seqno int, fmtdbpostdt datetime)
insert @NF03
          select 1001, 152, 3, '01/03/2006'
union all select 1001, 152, 5, '01/06/2006'
union all select 1001, 153, 7, '01/11/2006'

union all select 1002, 152, 2, '01/03/2006'

union all select 1003, 153, 3, '01/05/2006'
union all select 1003, 153, 4, '01/06/2006'

union all select 1004, 152, 2, '01/04/2006'
union all select 1004, 152, 5, '01/06/2006'

union all select 1005, 152, 1, '01/03/2006'
union all select 1005, 152, 3, '01/05/2006'
union all select 1005, 152, 4, '01/07/2006'
union all select 1005, 152, 6, '01/07/2006'
union all select 1005, 152, 7, '01/08/2006'

--e
print 'Row numbers for @NF03 - these are the numbers we''re aiming at for d so that we can then join the d and e'
    select *, (select count(*) from @NF03 where sccfno = c.sccfno and seqno <= c.seqno) as rowno
    from @NF03 c

--b
print 'Row numbers for @NF02 AND count of dates in @NF03 which are less than dates in @NF02
'
    select	*,
        (select count(*) from @NF02 where sccfno = a.sccfno and seqno <= a.seqno) as rowno,
        (select count(*) + 1 from @NF03 where sccfno = a.sccfno and fmtdbpostdt <= a.fmtdbpostdt) as minrowno
    from @NF02 a

--d
print 'Minimum of rowno and minrowno from b
'
    select *, case when minrowno > rowno then minrowno else rowno end as adjustedrowno from (
    select	*,
        (select count(*) from @NF02 where sccfno = a.sccfno and seqno <= a.seqno) as rowno,
        (select count(*) + 1 from @NF03 where sccfno = a.sccfno and fmtdbpostdt <= a.fmtdbpostdt) as minrowno
    from @NF02 a) b


--calculation
print 'join d to e using the calculated row numbers
'
select d.sccfno, d.rsncd, d.seqno, d.fmtdbpostdt,
       e.sccfno, e.rsncd, e.seqno, e.fmtdbpostdt
from
    (
    select *, case when minrowno > rowno then minrowno else rowno end as adjustedrowno from (
    select	*,
        (select count(*) from @NF02 where sccfno = a.sccfno and seqno <= a.seqno) as rowno,
        (select count(*) + 1 from @NF03 where sccfno = a.sccfno and fmtdbpostdt <= a.fmtdbpostdt) as minrowno
    from @NF02 a) b
    ) d
inner join
    (
    select *, (select count(*) from @NF03 where sccfno = c.sccfno and seqno <= c.seqno) as rowno
    from @NF03 c
    ) e
on d.sccfno = e.sccfno and d.adjustedrowno = e.rowno


Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.
Go to Top of Page

rajmalempati
Starting Member

8 Posts
Posted - 2006-05-16 : 12:05:16
Hi Ryan Randall

I am sorry to bother you,I got a small problem in our query.Look at below example.

Nf02 table
sccfno rsncd seqno fmtdbpostdt
1005, 165, 1, '01/10/2006'
1005, 152, 2, '01/12/2006'
1005, 152, 3, '01/12/2006'

NF03 table
sccfno rsncd seqno fmtdbpostdt
1005, 152, 4, '01/13/2006'
1005, 152, 5, '01/16/2006'
1005, 165, 6, '01/18/2006'

For the above scenario my right match is below
======================================================
1005, 165, 1, '01/10/2006' 1005, 165, 6, '01/18/2006'
1005, 152, 2, '01/12/2006' 1005, 152, 4, '01/13/2006'
1005, 152, 3, '01/12/2006' 1005, 152, 5, '01/16/2006'
=======================================================
Rsncd should always match.

Thanks
Go to Top of Page

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts
Posted - 2006-05-16 : 12:23:36
No worries.

We just need to join on rsncd everywhere we've joined on sccfno. See the adjusted query below...

--data
set dateformat mdy
declare @NF02 table (sccfno int, rsncd int, seqno int, fmtdbpostdt datetime)
insert @NF02
          select 1005, 165, 1, '01/10/2006'
union all select 1005, 152, 2, '01/12/2006'
union all select 1005, 152, 3, '01/12/2006'

declare @NF03 table (sccfno int, rsncd int, seqno int, fmtdbpostdt datetime)
insert @NF03
          select 1005, 152, 4, '01/13/2006'
union all select 1005, 152, 5, '01/16/2006'
union all select 1005, 165, 6, '01/18/2006'

--calculation
select d.sccfno, d.rsncd, d.seqno, d.fmtdbpostdt,
       e.sccfno, e.rsncd, e.seqno, e.fmtdbpostdt
from
    (
    select *, case when minrowno > rowno then minrowno else rowno end as adjustedrowno from (
    select	*,
        (select count(*) from @NF02 where sccfno = a.sccfno and rsncd = a.rsncd and seqno <= a.seqno) as rowno,
        (select count(*) + 1 from @NF03 where sccfno = a.sccfno and rsncd = a.rsncd and fmtdbpostdt <= a.fmtdbpostdt) as minrowno
    from @NF02 a) b
    ) d
inner join
    (
    select *, (select count(*) from @NF03 where sccfno = c.sccfno and rsncd = c.rsncd and seqno <= c.seqno) as rowno
    from @NF03 c
    ) e
on d.sccfno = e.sccfno and d.rsncd = e.rsncd and d.adjustedrowno = e.rowno


Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site