Horizontal Join??

[nsarun]

I have a query, that results in two rows for each Entity. Meaning, if it yields 8 rows, the effective # of rows are 4. How do I create a horizontal join of the two related rows to one row and hense result in just 4 rows instead of 8?

E.g.

Name Age Supervisor City State

Aron 22 Sam New York NY
Aron 22 Sam Kansas City KA

I want it to be

Aron 22 Sam Neyork NY Kansas City KC

Help me Geniuses!!

Arun

 

[PHV]

SELECT A.Name, A.Age, A.Supervisor, A.City, A.State, B.City, B.State
FROM yourTable A INNER JOIN yourTable B
ON A.Name = B.Name AND A.Age = B.Age AND A.Supervisor = B.Supervisor AND A.City < B.City

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[nsarun]

Thanks for the response. The thing is ux has many mx's each mx has one mc. So each ux has many mc's. I am trying to get uc and related mc's in a single record. Hence, I did something like this.

select ux.unit_slno,
ux.catalog_no,
ux.unit_status,
ux.prepare_date,
ux.prepared_by,
mc.MAC_Addr,
mc.sw_rev,
mc1.MAC_Addr,
mc1.sw_rev
from unit_module_info ux,
module_info mx
module_cfg mc,
INNER JOIN
module_cfg mc1
ON mx.unit_slno=ux.unit_slno
AND mc.module_slno=mx.module_slno

Didnt really give a single record. it gave many combinations. Can you correct me please....

 

[PHV]

I'm afraid you have to play with a stored procedure or a user function ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[r937]

play with" is indeed very descriptive of the process...



nsarun, did you see my posts on the other forums?

what you want can be achieved only with non-ANSI SQL in MySQL and Sybase ASE

if you have some other database system, you will have to retrieve the one-to-many data as a result set and then process it in the application layer

r937.com | rudy.ca