Mysql complicated query with join and count to do same work as subquery and to use it in zend framework

Question

I have a rather complicated query that I want to put into use with Zend Framework. In order to do this I needed to convert query from subqueries to joins.

This is the deal: I have 3 tables that need to be connected in order to form proper info. This can be accomplished using this query:

SELECT 
 ol.orders_id, 
 ol.photos_to_add, 
 ol.locations_id, 
 l.name, 
 (select 
   count(*) 
  from 
   photos as p 
  where 
   p.location_id = ol.locations_id and p.per_order = ol.orders_id) 
  as added 
FROM `order_has_locations` AS ol, 
 `locations` AS l 
WHERE ol.orders_id = 1 and l.id = ol.locations_id

I try to convert it to joins:

SELECT ol.orders_id, ol.locations_id, ol.photos_to_add, l.name, count(p.id) AS added
FROM order_has_locations as ol
INNER JOIN locations as l on l.id = ol.locations_id
left outer join photos as p on p.per_order = ol.orders_id
where p.location_id = ol.locations_id and ol.orders_id = 1

but this way the info about 0 photos is missing.

I tried many different approaches to no avail...

Nitin Midha · Answer 1 · 2012-09-07T18:00:47.773

0

That is because of p.location_id = ol.locations_id, move this condition from where to join clause, it should work. As currently it in where clause it is applied after join has evaluated, hence for 0 photos this condition turns out to be p.location_id = null which is false and hence rows are filtered out.

SELECT ol.orders_id, ol.locations_id, ol.photos_to_add, l.name, count(p.id) AS added
  FROM order_has_locations as ol
 INNER JOIN locations as l on l.id = ol.locations_id
  LEFT outer join photos as p on p.per_order = ol.orders_id 
   AND p.location_id = ol.locations_id 
 WHERE ol.orders_id = 1
 GROUP BY ol.orders_id, ol.locations_id, ol.photos_to_add, l.name

edited Sep 07 '12 at 18:00

answered Jul 13 '12 at 19:11

Nitin Midha

2,258
20
22

Well, I hoped that this would work, but unfortunately it doesn't - the return values are the same as for my original join-query. – Johnny_Bit Jul 13 '12 at 19:59
Can you give some example? input data and expected output data – Nitin Midha Jul 13 '12 at 20:15

score 0 · Answer 2 · answered Jul 14 '12 at 00:51

You need to use GROUP BY for aggregate functions. And left join is an outer join by definition, you needn't specify that.

SELECT ol.orders_id, ol.locations_id, ol.photos_to_add, l.name, count(p.id) AS added
FROM order_has_locations as ol
INNER JOIN locations as l on l.id = ol.locations_id
LEFT JOIN photos as p on p.per_order = ol.orders_id
where p.location_id = ol.locations_id and ol.orders_id = 1
GROUP BY p.id

Mysql complicated query with join and count to do same work as subquery and to use it in zend framework

2 Answers2