returning a multi row select within a single row select

From: LukeH <luke.horan_at_ansettworldwide.com>
Date: 7 Jul 2003 17:52:28 -0700
Message-ID: <e8e695c6.0307071652.7345a122_at_posting.google.com>


has anyone ever created a query that you have a table which links into a one to many that has a one to many relationship with a lookup table and you want to return a single row with the data from the first table and the data from the one to many.
example below of what i'm looking for, i would do it programatically but i'm using a generic peice of asp code that picks up sql from a directory depending on what a user has selected so it can only only be run once.
 cheers
LukeH      

table a   

key_a col_1 col_2 col_3 col_4   

1 	a 	a 	a 	a
2 	bb 	bb 	bb 	bb
3 	ccc 	ccc 	ccc 	ccc
 
 
table b			table c
 
key_a 	key_c		 key_c 	desc
 
1 	1		 1	 x
1	2		 2	 y
1	3		 3	 z
2	1	 	 2	 2
3	3 
 
 

data to be returned from query    

key_a col_1 col_2 col_3 column name here depends on whats to be returned as key_a will be used in the where clause. So only one row will be returned but need to be able to handle all cases below.  

1 	a 	a 	a 	x  	y	 z
2 	bb	bb	bb 	x 	y
3 	ccc 	ccc 	ccc 	z
Received on Tue Jul 08 2003 - 02:52:28 CEST

Original text of this message