CODEWITHSUNDEEP
mysqljoincount
StefanHanotin
StefanHanotin

Reputation: 301

MySQL Count rows from query with left joins

I have a query that looks like this:

 Select x.date, x.id, x.phone,
    x.product, xy.policy,xy.date 
 from (y left join z 
            on y.customer_id=z.customer_id)
    left join x 
       on x.id=z.id 
    left join xy 
       on xy.id=x.id 
 where x.date > '2000-01-01'  
    and y.detail =foo 
    and xy.policy like 'foo_____'  
    and xy.policy_type = foo;

How can I count the number of rows this returns?

I tried using SQL_CALC_FOUND_ROWS but I can't quite fit it into this query.

Any help would be greatly appreciated.

Thanks, Stefan.

Upvotes: 0

Views: 221

Answers (2)

Charles Bretana
Charles Bretana

Reputation: 146429

Easiest is just add a subquery... 

 Select x.date, x.id, x.phone,
    x.product, xy.policy,xy.date,
    (Select Count(*) 
     From (y left join z on y.customer_id=z.customer_id)
        left join x on x.id=z.id 
        left join xy  on xy.id=x.id 
     where x.date > '2000-01-01'  
       and y.detail =foo 
       and xy.policy like 'foo_____'  
       and xy.policy_type = foo) RecordCount  
 from (y left join z 
            on y.customer_id=z.customer_id)
    left join x 
       on x.id=z.id 
    left join xy 
       on xy.id=x.id 
 where x.date > '2000-01-01'  
    and y.detail =foo 
    and xy.policy like 'foo_____'  
    and xy.policy_type = foo;

If all you want is the count, then: 

 Select Count(*) 
 From (y left join z on y.customer_id=z.customer_id)
    left join x on x.id=z.id 
    left join xy  on xy.id=x.id 
 where x.date > '2000-01-01'  
   and y.detail =foo 
   and xy.policy like 'foo_____'  
   and xy.policy_type = foo

Upvotes: 1

ruakh
ruakh

Reputation: 183240

You can write:

SELECT COUNT(1)
  FROM y
  JOIN z
    ON y.customer_id = z.customer_id
  JOIN x
    ON x.id = z.id
  JOIN xy
    ON xy.id = x.id
 WHERE x.date > '2000-01-01'
   AND y.detail = foo
   AND xy.policy LIKE 'foo_____'
   AND xy.policy_type = foo
;

(Note that I've taken the liberty of changing your LEFT JOINs to regular JOINs, since the WHERE clause prevented them from actually functioning as LEFT JOINs anyway. If you want real LEFT JOINs, you can move conditions from the WHERE clause into ON clauses:

SELECT COUNT(1)
  FROM y
  LEFT
  JOIN z
    ON z.customer_id = y.customer_id
  LEFT
  JOIN x
    ON x.id = z.id
   AND x.date > '2000-01-01'
  LEFT
  JOIN xy
    ON xy.id = x.id
   AND xy.policy LIKE 'foo_____'
   AND xy.policy_type = foo
 WHERE y.detail = foo
;

)

Upvotes: 0

Related Questions