need a simple query

Question

Post reply

need a simple query

shiwani2002sg

Ten Centuries

Points: 1037
More actions
January 5, 2009 at 12:01 am

#127771

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

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

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

Paresh Prajapati SSCertifiable Points: 7567 More actions · Answer 2

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

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 3

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.

Ken McKelvey SSCoach Points: 18842 More actions · Answer 4

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

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

raj acharya SSCrazy Points: 2805 More actions · Answer 6

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

shiwani2002sg Ten Centuries Points: 1037 More actions · Answer 7

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.

shiwani2002sg Ten Centuries Points: 1037 More actions · Answer 8

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

but not fully

Paresh Prajapati SSCertifiable Points: 7567 More actions · Answer 9

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 10

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 11

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

J-440512 SSCertifiable Points: 6285 More actions · Answer 12

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 ?

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 13

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)