title: SQL Server multiple many-to-many join query
body: <p>I currently have a one main table with multiple other tables associated with it via many-to-many joins (with join tables). The application using this database needs to have search functionality that will print out multiple rows matching specific criteria, including all the values in the join tables. The values from the join tables also need to be links that will enable a search for all other rows that match that value. I am trying to figure out how to do this without taxing the database.</p>
  
  <p>Here is an example of the table structure</p>
  
  <pre><code>**Metrics (Main Table)** 
  MetricID (pk) 
  Metric
  
  **Domains (ValueList Table)** 
  DomainID (pk) 
  Domain
  
  **MetricsDomains (Join Table)** 
  MetricsDomainsID (pk) 
  MetricID (fk) 
  DomainID (fk)
  
  **MetricTypes (ValueList Table)** 
  MetricTypeID (pk) 
  MetricType
  
  **MetricsMetricTypes (Join Table)** 
  MetricMetricTypesID (pk) 
  MetricID (fk) 
  MetricTypeID (fk)
  
  **Studies (ValueList Table)** 
  StudyID (pk) 
  Study
  
  **MetricsStudies (Join Table)** 
  MetricsStudiesID (pk) 
  MetricID (fk) 
  StudyID (fk)
  </code></pre>
  
  <p>When someone searches for a Metric by various criteria, they should get output in table format that looks something like this: </p>
  
  <pre><code>Metric1 | Description | Study1, Study2, Study3 | MetricType1, MetricType2 | Domain1, Domain2
  Metric2 | Description | Study5, Study2, Study4 | MetricType2, MetricType3 | Domain5, Domain9
  </code></pre>
  
  <p>The Metric will be a link to the full description of the Metric. However, in addition, the Studies (ie., Study 1, Study 2, Study 3, etc.) and MetricTypes (MetricType1, Metric2, etc.) and Domains (Domain1, Domain 2, etc.) should also be links, that when clicked on, will perform a new search for all other metrics that contain that study, or type, or domain. This leads me to believe I will also need the primary key of the study, type, or domain, in addition to the text, in order to place in the href. </p>
  
  <p>At any rate, considering one search could possibly return 20+ metrics, what I need to figure out is a good way to write an optimized query to return the results of the multiple many-to-many joins. I know that joining all of these tables in one query will generally result in a Cartesian product of all the joins, but I am not sure if there is another way to go about it. I have also read about a way I could return the many-to-many results as a comma-separated list in a field using a method like this:</p>
  
  <pre><code>SELECT m.MetricID, Description, 
      STUFF((
      SELECT ', ' + s.Study
      FROM Studies s, Metrics_Studies ms 
      WHERE s.StudyID = ms.StudyID AND ms.MetricID = m.MetricID
      ORDER BY s.Study
      FOR XML PATH('')
      ),1,1,'') as Study, 
  FROM Metrics m 
  WHERE Metric_PK = 13
  </code></pre>
  
  <p>However, I am not sure of the performance impact of this method, or whether it will really get me what I am looking for since I think I may need the primary keys of the Studies as well.</p>
  
  <p>Any help would be appreciated.</p>
  
  <p>Thanks!</p>