How To !! - Query Master Detail tables and return flat result set

Hi,

I need to query a master and detail table but returns records in on result set with each row containing the header plus a field for each detail. i.e.

MasterTable
Record1-Field1-Field2

DetailTable
Record1-Field1
Record2-Field1
Record3-Field1

The two tables are linked with an integer ID. I need to return for each master record the following:-

ResultRecord-MasterField1-MasterField2-DetailRecord1Field1-DetailRecord2Field1-DetailRecord3Field1

Any help would be appreciated as I'm quite new to this.

regards,
Simon.

LEOJZAMBONI
Thank you for the reply. My problem is slightly different though than that example.

I need to join a master table with multiple records from the detail table returning only one record containing the master table fields and all the detail fields for each detail record.

MasterTable
  ItemID
MasterText

DetailTable
  ItemID
  DetailText

Assuming DetailTable is linked to MasterTable on ItemID and there are multiple detail records, I need to return the following result.

ItemID, MasterText, DetailRecord1.DetailText, DetailRecord2.DetailText .......

rather than the normal:-

ItemID,MasterText,DetailRecord1.DetailText
ItemID,MasterText,DetailRecord2.DetailText
......

regards,

Simon.
Tore Birkeland
It is possible. with SQL 2000. However the number of column returns will be finite.
Post your table DDL, some sample data & expected result.
js12
Unfortunately I am using sql 2000, you know of any other way to do it
Robert U
See http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=140320&SiteID=1
Henrik Andre Tellevik
If you don't mind using proprietary extensions, check out the new SQL keyword "pivot" in SQL Server 2005.
How To !! - Query Master Detail tables and return flat result set