duplicate data being return with varied # of results in cross join

I created a database for a clothing brand. The products consists of 3 tables: products, product_photos, inventory.

The issue I’m having pertains to the amount of results in the product_photos, inventory tables that are returned. So say there should be 4 results in the inventory table (ex. size: s, m, l, xl AND quantity: 20, 20, 20, 20) and 1 result in the product_photos table (ex. photos: url). With my current query the product_photos data is duplicated 4 times (ex. photos: url, url, url, url).

My current query looks like this:

SELECT 
  products.id, 
  products.type, 
  products.collection, 
  products.title, 
  products.price, 
  products.thumbnail, 
  GROUP_CONCAT(product_photos.id) AS photoId, 
  GROUP_CONCAT(product_photos.photo) AS photos, 
  GROUP_CONCAT(inventory.size) AS size,                                  
  GROUP_CONCAT(inventory.quantity) as quantity 
FROM `products` 
RIGHT JOIN 
`product_photos` ON products.id = product_photos.product_id 
RIGHT JOIN `inventory` ON products.id = inventory.product_id 
WHERE
products.id = ?
GROUP BY
products.id

I have played around with some things such as changing the right’s to inner join’s and left join’s but right join seems to be the technical choice for what I’m trying to do.

Here is some sample data:

product:
 id: 1

product_photo:
 id: 1,
 product_id: 1,
 photo: url

inventory:
 id: 1,
 product_id: 1,
 size: s,
 quantity: 20

 id: 2,
 product_id: 1,
 size: l,
 quantity: 14

Answer

The reason for the many results is that both the product_photos as and the inventory table can have multiple records for the same product_id and so all pairwise combinations of records from both tables will be iterated (Cartesian product).

You can solve this by first selecting all records from both these tables with a union and then join that result with the products table:

SELECT      products.id, 
            products.type, 
            products.collection, 
            products.title, 
            products.price, 
            products.thumbnail,
            GROUP_CONCAT(photoId) AS photoId,
            GROUP_CONCAT(photos) AS photos,
            GROUP_CONCAT(size) AS size,                                  
            GROUP_CONCAT(quantity) as quantity 
FROM        products
LEFT JOIN   (
                SELECT  product_id, 
                        id AS photoId, 
                        photo AS photos,
                        null AS size,
                        null AS quantity
                FROM    product_photos
                UNION
                SELECT  product_id,
                        null,
                        null,
                        size,                                  
                        quantity 
                FROM    inventory 
            ) combi
         ON products.product_id = combi.product_id
WHERE       products.id = ?
GROUP BY    products.id