Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2004
    Posts
    1

    Question Unanswered: 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.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2003
    Location
    India
    Posts
    523
    I agree with rudy, tried playing with the query but could not come up with anything.

  4. #4
    Join Date
    Mar 2004
    Location
    Venice,Italy
    Posts
    20
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •