How to count matched rows with left joins and multiple where conditions

Question

I am trying to fetch posts with meta values where posts have some meta value. for pagination i need count of all rows matched with query so i can divide that into pages and serve to frontend. But I think i am missing some fundamental concept here, I am getting count of 1 every time even when more rows are matched.

^ this is what i get after running in phpmyadmin

SELECT
    COUNT(*) AS total_rows,
    p.id,
    p.title,
    m.meta_key,
    m.meta_value
FROM
    posts p
LEFT JOIN meta m ON
    p.id = m.object_id AND m.table_name = "post"
WHERE
    m.meta_key = 'post_taxonomy' AND m.meta_value = 'banner' AND p.post_type = 'post'
GROUP BY
    p.id

But i want something like following screenshot

Please [use text, not images/links, for text--including tables & ERDs](https://meta.stackoverflow.com/q/285551/3404097). Use images only for what cannot be expressed as text or to augment text. Include a legend/key & explanation with an image. — philipxy, Oct 15 '20 at 12:15
Duplicate of [Left Join With Where Clause](https://stackoverflow.com/q/4752455/3404097) This is a faq. Please before considering posting read your textbook and/or manual & google any error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names & site:stackoverflow.com & tags; read many answers. Reflect your research. See [ask] & the voting arrow mouseover texts. If you post a question, use one phrasing as title. — philipxy, Oct 15 '20 at 12:16
Whatever other problems you have, you are using left join & where in the way described in the duplicate link.--So you could change the left to inner & alway get the same result. So whatever other problems you have, you are using left instead of inner for no reason. This is explained in an answer. Re other problems see my next comment repeating the 1st comment above. Not acting on that comment is another reason to close a post. When you act, your problem will be another faq. Re unwelcoming you have no justification for thinking that. Please educate yourself re how the site works. [help] — philipxy, Oct 16 '20 at 05:25
Please in code questions give a [mre]--cut & paste & runnable code, including smallest representative example input as code; desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. Give the least code you can that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) For SQL that includes DBMS & DDL (including constraints & indexes) & input as code in table format. [ask] Pause work on the overall goal, chop code to the 1st expression not giving what you expect & say what you expect & why. — philipxy, Oct 16 '20 at 05:25

score 0 · Accepted Answer · answered Oct 15 '20 at 07:19

0

Try this:

SELECT 
  COUNT(p.id) as count 
FROM (
  SELECT
      p.id,
      p.title,
      m.meta_key,
      m.meta_value
  FROM
      posts p
  LEFT JOIN meta m ON
      p.id = m.object_id AND m.table_name = "post"
  WHERE
      m.meta_key = 'post_taxonomy' AND m.meta_value = 'banner' AND 
      p.post_type = 'post'
) t

answered Oct 15 '20 at 07:19

Mech

3,952
2
14
25

1

oh this is working , just for my further improvement , what are your views on performance of this type of query ? – harshit mishra Oct 15 '20 at 07:30
It depends on your database setup. Run some tests on it and see what works best for you. – Mech Oct 15 '20 at 07:31

score 0 · Answer 2 · answered Oct 15 '20 at 11:47

First, simply run COUNT(*) in the select.

Second, the LEFT JOIN is superfluous; the WHERE clause turns it into an INNER JOIN. So, you might as well have clear SQL.

So:

SELECT COUNT(*)
FROM posts p JOIN
     meta m
     ON p.id = m.object_id AND m.table_name = 'post'
WHERE m.meta_key = 'post_taxonomy' AND
      m.meta_value = 'banner' AND 
      p.post_type = 'post';

Also, enclose strings in single quotes, not double quotes. Single quotes are the SQL standard for delimiting strings.

How to count matched rows with left joins and multiple where conditions

2 Answers2