0

After spending a few months pondering how i was going to "JOIN" tables together which share same named columns and outputting them in a format which can be searched and paged i came up with this solution, whereby it creates a "column" with a set name, such as stock_levels and enters the values as JSON so it can be parsed out afterwards.

This works exactly as intended, however the problem i have come accross is the "WHERE" section, ( product.id = quantity.product_id ) returns all of the elements in quantity which the id of product, however if there arnt any elements in quantity with that product id, the product itself is not returned, as new products entered into the system neither have any stock or images, they are not return by the system.

My question is thus, is there a way of basically doing this ( but ofc with code that works )

WHERE ( `product`.`id` = `quantity`.`product_id` OR COUNT( `product`.`id` = `quantity`.`product_id` ) = 0 )

( ie return the product if it has no entries in quantity )

And here is the code of the query used:

SELECT DISTINCT `product`.*, 
CONCAT( '[', GROUP_CONCAT( '{', '"id":"', `quantity`.`id`, '"', ',', '"sku":"', `quantity`.`sku`, '"', ',', '"product_id":"', `quantity`.`product_id`, '"', ',', '"criteria":"', `quantity`.`criteria`, '"', ',', '"quantity":"', `quantity`.`quantity`, '"', ',', '"price":"', `quantity`.`price`, '"', '}' SEPARATOR ',' ), ']' ) as `stock_levels`, 
CONCAT( '[', GROUP_CONCAT( '{', '"id":"', `product_image`.`id`, '"', ',', '"product_id":"', `product_image`.`product_id`, '"', ',', '"location":"', `product_image`.`location`, '"', ',', '"type":"', `product_image`.`type`, '"', ',', '"order":"', `product_image`.`order`, '"', '}' SEPARATOR ',' ), ']' ) as `images`
FROM (`product`, `quantity`, `product_image`)
WHERE ( `product`.`id` = `quantity`.`product_id` ) 
AND ( `product`.`id` = `product_image`.`product_id` ) 
AND `online` = 1
GROUP BY `product`.`id`
ORDER BY date_added desc
Chris Cooper
  • 25
  • 3

2 Answers2

0

I think you want a left-outer join to the quantity table... This means whatever record in the table on the left side of the join will ALWAYS be included regardless of a match to the right side of the join...

select ...
     from product left outer join quantity
           on product.id = quantity.product_id,
          product_image
     where 
          product.id = product_image.product_id
DRapp
  • 47,638
  • 12
  • 72
  • 142
0

You want to convert your inner joins to Left Joins

FROM
 product p
 LEFT JOIN quantity q
 ON p.id = q.product_id
 LEFT JOIN product_image pi
 ON p.id = pi.product_id 
WHERE
 p.online = 1

This assumes that online is in the product table if its not you'll need to move it into the JOIN.

Also you should be using ANSI 92 joins anyway

Conrad Frix
  • 51,984
  • 12
  • 96
  • 155
  • Works a charm, im not familiar with ANSI 92 JOINS, is that i should be using joins or are they a specific variation of them. – Chris Cooper Jan 18 '11 at 02:40