Search code examples
mysqlmodx

MySQL Join not Producing Desired Results

I'm working on writing a snippet for ModX that will find all document with the specified TV set to a user submitted value.

Here is a description of the tables I'm working with. http://wiki.modxcms.com/index.php/Template_Variable_Database_Tables

Here is my query:

SELECT contentid 
FROM prefix_site_tmplvar_contentvalues
JOIN prefix_site_tmplvar_contentvalues
ON prefix_site_tmplvars.id = prefix_site_tmplvar_contentvalues.tmplvarid
WHERE value="Red"

Currently it's producing results such as this: http://pastebin.com/mEJ1w2be

Where each document ID will have a new row in the results for each Template Variable. So, for 7455 in the example there will be one array for the color="red" one for material="wood" one for size="small". Which, makes it difficult if I want to find a product that is red, small, and made of wood.

Is there a way that I could join these tables so that I could get one row per product with the document id and a set of template variable with associate values—not all broken up?

Solution

  • try

    GROUP BY contentid

    this will smush all the rows with the same contentid together.