Complex SQL Query

Hi all,

I am developing an application using SQL Server as Back-end. I am facing a problem in creating a SQL Query. The details are as follows:

There are three tables in the Database, Data Type of all Columns is Numeric in all three tables:

1. T1

(column names and sample data)

en
==

1
2
3

2) T2

(column names and sample data)

en gn
== ==

1 10
1 11
2 10
2 12
2 13

3) T3

(column names and sample data)

en pn
== ==

1 20
1 21
1 22
2 20

Now I have to create a SQL Query, whereby I can get the following result:

en gn pn
== == ==
1 10 20
1 11 21
1 NULL 22
2 10 20
2 12 NULL
2 13 NULL

I have tried various combination of Joins, but unable to get the desired result as the tables have many-to-many relationships, therefore I get many duplicate rows in the result. UNION will not solve the problem, as that will add the additional rows for the third table. Although I can achieve this by writing few lines of code, but I have to create a SQL Query for getting this result. Kindly tell me the way for creating the required Query for this. Many Thanks for your help.there does not seem to be any join criterion

how do you know gn=10 matches pn=20?

try stating the join criterion on english, and i don't think you can do it

you may have to do your "matching" with application code|||I agree with rudy, tried playing with the query but could not come up with anything.|||I think this is what you search for:

create table ##tmp1 (en Int,pn Int,ref Int);
create table ##tmp2 (en Int,gn Int,ref Int);

insert into ##tmp1
select
a.en,
a.pn,
count(b.en) ref
from t3 a,t3 b
where a.en=b.en and a.pn>=b.pn
group by a.en,a.pn;

insert into ##tmp2
select
a.en,
a.gn,
count(b.en) ref
from t2 a,t2 b
where a.en=b.en and a.gn>=b.gn
group by a.en,a.gn;

select
coalesce(##tmp1.en,##tmp2.en) en,
##tmp1.pn,
##tmp2.gn
from ##tmp1
full join ##tmp2 on ##tmp1.en=##tmp2.en and ##tmp1.ref=##tmp2.ref
order by en