How to query multiple primary records and their one-to-many records efficiently

Consider the following structure DB structure. Current working language is Coldfusion and MSSQL.

Tables
Hotels: - Columns - hotelid, name, company, address, state, city, zip
Media: - Columns - mediaid, label, url
HotelsXMedia: - Columns - xid, hotelid, mediaid

Basically we have a table with hotels and a table that contains images and a cross table to handle the many to many relationship. Desired output is a single array (or query), with each item having all of the columns from the hotels table as well as the all of the rows of it's media table inside the primary row so it can be passed on to a theming function, for example.

Current Method is to query hotels, convert to array, extract hotelids from result set. Query media based on hotelid. Convert resulting media query into structure where the index is the hotelid. Then loop over the hotels array and assign in it's media data into the hotels array. Then pass the hotels array on to wherever it needs to go.

Is there a more efficient way of doing this? Is there a way of doing this without doing two SQL queries and all of the resulting folding and looping. Nearly every module in the application utilizes the media table to store it's images so for nearly every component on the page we have to perform this sort of combination and it seems quite cumbersome. Is there no better way? In addition, while it may only provide a slight benefit to enhance this one to many query combination, because it's neccessary in 90% of the components, the enhancement benefit would spread to all of them thus resulting in decent gains.

I would consider it a feasible solution if a column in the result query was a delimited list of joined MEDIA urls. But unfortunately in MSSQL we don't ha开发者_StackOverflowve group_concat(). If this method was done is there an easy way to grab multiple columns, if for example I wanted to grab the media:url and media:label? Is there a way to ensure that empty or null values still get a delimiter if a record in the media table has a url but lacks a label thus causing the 2 lists to be out of alignment. If I went this route would I have to do multiple for XML paths in order to get there? Any guidance would be greatly appreciated.

Check out Simulating group_concat MySQL function in Microsoft SQL Server 2005?

When I was faced with this problem, I ended up making one big query (select * from hotelsxmedia join hotels on ... join media on ...), which returns as many rows as the rows of the cross table (HotelsXMedia). Just be sure to order by hotelid. Then when you loop through the query results, you can take one action for every new hotelid (eg, append to array) and a different action for a row that repeats the same hotelid (eg, add the new media to it).

As @krubo suggests, I'd do one large query and then do a cfoutput using the group="hotelid" attribute and create your array and structs within that.

You can do this all in one query ordered by hotelid and build the data structure by using a grouped nested CFOUTPUT.

<cfquery name="myHotels" datasource="HotelStuff" >
  SELECT H.*, M.*
  FROM Hotels H
      JOIN HotelsXMedia HXM ON H.hotelid = HXM.hotelid
      JOIN Media M ON M.mediaid = HXM.mediaid
  ORDER BY H.hotelid
</cfquery>

The group="" attribute to CFOUTPUT causes the loop to be grouped by changes in the hotelid field value.

<cfoutput query="myHotels" group="hotelid">
  <cfset hotels = {
     hotelid = myHotels.hotelid,
     name = myHotels.name,
     company = myHotels.company,
     address = myHotels.address,
     state = myHotels.state,
     city = myHotels.city,
     zip = myHotels.zip,
     media = arrayNew(1)
  } />

  <cfoutput>
     <cfset thisMedia = { label = myHotels.label, url = myHotels.url } />
     <cfset arrayAppend(hotels.media, thisMedia) />
  </cfoutput>
</cfoutput>

You could order by Hotels.name and sometimes get away with it; you'd need to make sure that no two hotels ever have exactly the same name or they will be collapsed into a single group. It's generally safer to use a group based on your primary key column.