MySQL Count rows from query with left joins

Question

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.

can you outline the query and add some code tags around please? — Ruben
– Ruben, Commented Nov 20, 2012 at 15:53
do you want to have another column for the total rows of the result? eg ID, ColA, TotalResult, 1,1,4,1,2,4,1,3,4,1,4,4? — John Woo
– John Woo, Commented Nov 20, 2012 at 15:55
No thanks, I just want to get a count of what this returns. Basically if I could wrap a count() around this I would be done. — StefanHanotin
– StefanHanotin, Commented Nov 20, 2012 at 15:56

Charles Bretana · Accepted Answer · 2012-11-22 20:22:18Z

1

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

edited Nov 22, 2012 at 20:22

answered Nov 20, 2012 at 15:56

Charles Bretana

147k24 gold badges158 silver badges219 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

StefanHanotin Over a year ago

Hi Charles, your query is returning the correct count but all I want is the count. I don't want the table. I just want a single figure that counts the number of rows.

ruakh · Accepted Answer · 2012-11-20 15:58:42Z

0

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
;

)

answered Nov 20, 2012 at 15:58

ruakh

185k29 gold badges292 silver badges324 bronze badges

2 Comments

ruakh Over a year ago

@StefanHanotin: O.K.; did you understand my explanation about the LEFT JOINs and the WHERE clause?

StefanHanotin Over a year ago

I did understand your comment. Your top query worked as expected. Not sure why the bottom one is getting a different count.

Collectives™ on Stack Overflow

MySQL Count rows from query with left joins

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related