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

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2009-12-09 : 04:55:11

Dear All,
I am using the following query to join 2 tables

select * from AR_Snapshot s
inner join
AR_issues a on s.snp_Invoice_Number=a.Invoice_number
Now its returning duplicate records from AR_Snapshot.

I need distinct data from AR_Snapshot table.

Please help.

Thanks,

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-12-09 : 05:06:14

use distinct keyword or row_number to get distinct data

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2009-12-09 : 05:11:40

In spite of using the distinct key word i am getting duplicate records

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2009-12-09 : 05:18:01

Actually there is primary key on 2 columns snp_Invoice_Number and snp_Post_Period_EOM.
Even if i run with the distinct i am getting duplicate records

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-12-09 : 05:19:33

can u show some sample data from two tables and sample output..........

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2009-12-09 : 05:23:16

AR_issues table:
Invoice number
1
2
3

AR_Snapshot table:
snp_Invoice_Number/ enpdate
1 / 2009-10-01
1 / 2009-11-02
2 / 2008-01-19
3 / 2009-10-13
3 / 2009-04-14

Hopes it will help u give solution

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-12-09 : 05:25:12

can u post ur sample output

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2009-12-09 : 05:27:35

snp_Invoice_Number/ enpdate
1 / 2009-10-01
2 / 2008-01-19
3 / 2009-10-13

I need to get the only one record for each invoice_number of AR_issues table

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-12-09 : 05:33:48

select distinct s.snp_invoice_number,max(s.enpdate) enpdate
from AR_Snapshot s
inner join
AR_issues a on s.snp_Invoice_Number=a.Invoice_number
group by s.snp_invoice_number

vikky
Yak Posting Veteran

54 Posts

Posted - 2009-12-09 : 05:38:54

hi,

select snp_Invoice_Number,min(enpdate) from @AR_Snapshot s
inner join
@AR_issues a on s.snp_Invoice_Number=a.Invoicenumber
group by snp_Invoice_Number

Thanks,
vikky.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources