Nested SQL queries ?

[zainabk]

hi,

i have 2 tables

T1
col1, col2, col3
ID1 , col2Val1 col3Val1
ID2 , col2Val2 col3Val2
ID3 , col2Val3 col3Val3

T2
field1 , field2, field3
ID1 field2Val1 field3Val1
ID1 field2Val2 field3Val2
ID2 field2Val3 field3Val3
ID2 field2Val4 field3Val4

i.e T2.field1 is a foriegn key from T1.col1

i need an o/p that looks like

ID1, col2Val1, col3Val1, field2Val1, field3Val1, field2Val2, field3Val2
ID2, col2Val2, col3Val2, field2Val3, field3Val3, field2Val4, field3Val4

[nico5038]

Hmm, looks hard to do with queries.
Does your table T2 always hold two rows for each ID or can they vary ?

Nic;o)

[zainabk]

Hmm, looks hard to do with queries.
Does your table T2 always hold two rows for each ID or can they vary ?

Nic;o)

not two , but T2 has a fixed no. of rows corresponding to each entry in T1

[nico5038]

OK when it's fixed you can use the MOD function to "aggregate" the rows into one new row with all data.
Will look for two rows like:
[code=sql]
select id, field1, field2, "" as field3, ""as field4 from T2 where Autonum MOD 2 = 1
UNION
select id, "" as field1, "" as field2, field3, field4 from T2 where Autonum MOD 2 = 0
[code]

This does require to create an Autonumber field before code execution to enable the MOD to work.
Finally A GroupBy query will be needed to get the MAX value of the field values into one row.

Getting the idea ?

Nic;o)

[zainabk]

I managed to do it !

Thanx !!

[nico5038]

Well done !

Success with your application !

Nic;o)