How to display 3 columns from different table into one query

Question

How to display 3 columns from different table into one query

arnold-491793

Say Hey Kid

Points: 709
More actions
November 18, 2008 at 6:14 pm

#75368

Hi Experts,
Need your help on how to display my 3 columns coming from 3 different tables. I have 3 Tables with 1 column.
create table DimLab1 (idno int,
WidthData float )
insert into dimlab1 values (1,2.2)
insert into dimlab1 values (2,3.2)
insert into dimlab1 values (3,2.5)
create table DimLab2 (idno int,
WidthData float )
insert into dimlab2 values (1,6.2)
insert into dimlab2 values (2,5.2)
insert into dimlab2 values (3,8.5)
create table DimLab3 (idno int,
WidthData float )
insert into dimlab3 values (1,3.3)
insert into dimlab3 values (2,8.2)
insert into dimlab3 values (3,7.5)
select a.widthdata, b.widthdata, c.widthdata
from dimlab1 a, dimlab2 b, dimlab3 c
I need to display the widhdata from dimlab1, dimlab2, dimlab3 in 3 consecutive row but it display 27 rows when i use the select query.
Any advice?
Appreciate your help.

Viewing 15 posts - 1 through 15 (of 16 total)

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

Garadin One Orange Chip Points: 29613 More actions · Answer 1

Please read the article in my signature on how to post table definition / sample data here. This could be very simple or somewhat difficult depending on the structure of these 3 tables.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

rosh-396756 SSCrazy Points: 2338 More actions · Answer 2

Would the number of rows always remain the same in all tables?

Suggestion: Please don't edit the post in a way by which the continuity of the thread is affected.

arnold-491793 Say Hey Kid Points: 709 More actions · Answer 3

yes, the number of rows remains the same in all tables.

rosh-396756 SSCrazy Points: 2338 More actions · Answer 4

Would this help

SELECT a.widthdata AS col1,

b.widthdata AS col2,

c.widthdata AS col3

from dimlab1 a

inner join dimlab2 b

on a.idno = b.idno

inner join dimlab3 c

on b.idno = c.idno

Kishore.P SSCrazy Eights Points: 8147 More actions · Answer 5

arnold (11/18/2008)
yes, the number of rows remains the same in all tables.

could you tell what exact output of query.

krayknot SSCertifiable Points: 7998 More actions · Answer 6

arnold (11/18/2008)
Hi Experts,
Need your help on how to display my 3 columns coming from 3 different tables. I have 3 Tables with 1 column.
create table DimLab1 (idno int,
WidthData float )
insert into dimlab1 values (1,2.2)
insert into dimlab1 values (2,3.2)
insert into dimlab1 values (3,2.5)
create table DimLab2 (idno int,
WidthData float )
insert into dimlab2 values (1,6.2)
insert into dimlab2 values (2,5.2)
insert into dimlab2 values (3,8.5)
create table DimLab3 (idno int,
WidthData float )
insert into dimlab3 values (1,3.3)
insert into dimlab3 values (2,8.2)
insert into dimlab3 values (3,7.5)
select a.widthdata, b.widthdata, c.widthdata
from dimlab1 a, dimlab2 b, dimlab3 c
I need to display the widhdata from dimlab1, dimlab2, dimlab3 in 3 consecutive row but it display 27 rows when i use the select query.
Any advice?
Appreciate your help.

what is the output??

kshitij kumar
kshitij@krayknot.com
www.krayknot.com

arnold-491793 Say Hey Kid Points: 709 More actions · Answer 7

Please take note that their IDNO does not have any relationship between each table.

i need an output like this irregardless of IDNO:

Dimlab1Widthdata Dimlab2Widthdata Dimlab3Widthdata

2.2 6.2 3.3

3.2 5.2 8.2

2.5 8.5 7.5

Thanks

ChiragNS One Orange Chip Points: 26137 More actions · Answer 8

I suggest you created temp tables with identity columns. Insert your data in order and then select the data by joining on the identity columns.

"Keep Trying"

arnold-491793 Say Hey Kid Points: 709 More actions · Answer 9

Chirag (11/18/2008)
I suggest you created temp tables with identity columns. Insert your data in order and then select the data by joining on the identity columns.

Thanks for your advice and I think it's possible in that way.

Is there a better way to display it in only one select statement without using the memory resources?

ChiragNS One Orange Chip Points: 26137 More actions · Answer 10

Whats the amount of data that we are dealing with?

"Keep Trying"

arnold-491793 Say Hey Kid Points: 709 More actions · Answer 11

Chirag (11/19/2008)
Whats the amount of data that we are dealing with?

Thousand of rows.

Dangol SSC Rookie Points: 49 More actions · Answer 12

I try this one and it works, I beginners just practicing...

arnold-491793 Say Hey Kid Points: 709 More actions · Answer 13

sarbhika (11/19/2008)
I try this one and it works, I beginners just practicing...

What did your try?

ChiragNS One Orange Chip Points: 26137 More actions · Answer 14

arnold (11/19/2008)
Chirag (11/19/2008)
Whats the amount of data that we are dealing with?
Thousand of rows.

I believe you could do this using complex T-SQL but even that would consume memory. Using temp tables has an advantage that they can have indexes. Using proper indexes can speed up your queries.

BTW thousands of rows is quite normal in most cases. Should not be a problem.

"Keep Trying"