need a simple query

  • hay

    There exist 2 tables

    table1

    A B C

    1 554 dd

    1 554 fr

    1 554 gh

    1 453 rf

    2 342 rd

    3 345 ed

    Table2

    A D F

    1 ed e

    1 gd 3

    1 er 3

    1 de 24

    2 et df

    3 ed dfd

    needed output

    A B C D

    1 554 dd ed

    1 554 fr gd

    1 554 gh er

    1 453 rf de

    2 342 rd et

    3 345 ed ed

    please suggest as early as possible

  • How are the two tables related?

    What makes dd relate to ed and fr to gd?

    If it's the order of the rows, then what column defines that order?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • shiwani2002sg (1/5/2009)


    hay

    There exist 2 tables

    table1

    A B C

    1 554 dd

    1 554 fr

    1 554 gh

    1 453 rf

    2 342 rd

    3 345 ed

    Table2

    A D F

    1 ed e

    1 gd 3

    1 er 3

    1 de 24

    2 et df

    3 ed dfd

    needed output

    A B C D

    1 554 dd ed

    1 554 fr gd

    1 554 gh er

    1 453 rf de

    2 342 rd et

    3 345 ed ed

    please suggest as early as possible

    select t1.A,t1.B,t1.C,t2.D

    from table1 t1 inner join table2 t2 on (t1.A=t2.A)

    _____________________________________________________________________________________________________________
    Paresh Prajapati
    ➡ +919924626601
    http://paresh-sqldba.blogspot.com/[/url]
    LinkedIn | Tweet Me | FaceBook | Brijj

  • Paresh Prajapati (1/5/2009)select t1.A,t1.B,t1.C,t2.D

    from table1 t1 inner join table2 t2 on (t1.A=t2.A)

    You may want to test your code, unfortunatelly it doesn't work.

    I think it will be better to wait for original poster to answer Gail's questions. 😉

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
  • It does not make a great deal of sense, but, for the given data, something like the following should work:

    SELECT D1.A, D1.B, D1.C, D2.D

    FROM

    (

    &nbsp&nbsp&nbsp&nbspSELECT ROW_NUMBER() OVER (PARTITION BY A ORDER BY C) AS AID

    &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp,*

    &nbsp&nbsp&nbsp&nbspFROM table1

    ) D1

    &nbsp&nbsp&nbsp&nbspJOIN

    &nbsp&nbsp&nbsp&nbsp(

    &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspSELECT ROW_NUMBER() OVER (PARTITION BY A ORDER BY E DESC, D DESC) AS AID

    &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp,*

    &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspFROM table2

    &nbsp&nbsp&nbsp&nbsp) D2

    &nbsp&nbsp&nbsp&nbspON D1.A = D2.A

    &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspAND D1.AID = D2.AID

  • Works like a charm Ken - at least, it produces the expected output from the given sample - but you need to change SELECT ROW_NUMBER() OVER (PARTITION BY A ORDER BY E DESC) AS AID to SELECT ROW_NUMBER() OVER (PARTITION BY A ORDER BY D DESC) AS AID

    Cheers

    ChrisM

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • Hi,

    as per you mention for data you want on which column you want to create relationship i think

    select t1.A,t1.B,t1.C,t2.D

    from table1 t1 inner join table2 t2 on (t1.A=t2.A)

    this query will give you output you want but for that you must have same data on both above column

    it make's inner join on both table

    Raj Acharya

  • hay all

    Well i want relation to be generated on the attribute 'A' of both the tables. but in both table A does not include any keys. and its stated that the value of A would be same in both the tables , row-by-row.

    I have thought another concept

    that i can open 2 cursors , 1 for table 1, and 2 for table2

    then create a new table and insert the both table 1 & 2 's values into newly created table,row by row , only then i wud be able to get desired results.

    suggestions still required.

  • querry given above for inner join works a bit if i add distinct(t1.A) ..

    but not fully

  • PaulB (1/5/2009)


    Paresh Prajapati (1/5/2009)select t1.A,t1.B,t1.C,t2.D

    from table1 t1 inner join table2 t2 on (t1.A=t2.A)

    You may want to test your code, unfortunatelly it doesn't work.

    I think it will be better to wait for original poster to answer Gail's questions. 😉

    This is the corredct one..

    _____________________________________________________________________________________________________________
    Paresh Prajapati
    ➡ +919924626601
    http://paresh-sqldba.blogspot.com/[/url]
    LinkedIn | Tweet Me | FaceBook | Brijj

  • shiwani2002sg (1/5/2009)


    and its stated that the value of A would be same in both the tables , row-by-row.

    This looks like homework, but at least you're putting in some thought and effort. Have you tried Ken's solution? It generates your desired output exactly.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • shiwani2002sg (1/5/2009)


    hay all

    Well i want relation to be generated on the attribute 'A' of both the tables. but in both table A does not include any keys. and its stated that the value of A would be same in both the tables , row-by-row.

    The question is what defines the order. If there's no column that specifies what order you want to control the matches, there's no way to guarantee that order. In SQL order or rows inserted does not determine the order the rows will be returned in, and there's no 'Row number'

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • From the listings given for both tables, I can see no primary key.

    So you might first give a serious look at the design of the database.

    How do you want to handle the cases where there are duplicate rows in the first table ?

    In the second table ? in the first and second table ?

  • I agree... there's nothing in the data presented that identifies which row in the first table goes with which row in the second table other than physical order listed... and we all know that is not maintained in an RDBMS. There must be some other information to do this correctly. All of the solutions rendered so far, are doomed to eventual failure because of the lack of proper join information... unless you don't really care about what the order is.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply