Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – How to make an analog of COUNT(from another table) when requesting SELECT after WHERE

I need a query that, when the conditions are met, takes into account the number of occurrences of a certain record in COUNT(*).

My non-working solution:

SELECT t.name, t.tovar_id, p.path FROM `tovars` t, `tovarPicture` p WHERE t.nomGroup = :group AND p.tovar_id = t.tovar_id AND p.tovarPicture_id IS NULL AND COUNT(SELECT * FROM knotConnections WHERE tovar_id = t.tovar_id) = 0
Tags:

2

Answers


  1. 0

    If the count must always be zero (ie, there are no matching records in the other table) then you could use NOT EXISTS or an anti-join:

    SELECT t.name, t.tovar_id, p.path
FROM `tovars` t
INNER JOIN`tovarPicture` p ON t.tovar_id = p.tovar_id
LEFT JOIN knotConnections ON t.tovar_id = knotConnections.tovar_id
WHERE t.nomGroup = :group
AND p.tovarPicture_id IS NULL
AND knotConnections.tovar_id IS NULL

    If you need more flexibility – you want the count to be a specific number – then you could use COUNT in a HAVING clause:

    SELECT t.name, t.tovar_id, p.path
FROM `tovars` t
INNER JOIN`tovarPicture` p ON t.tovar_id = p.tovar_id
LEFT JOIN knotConnections ON t.tovar_id = knotConnections.tovar_id
WHERE t.nomGroup = :group
AND p.tovarPicture_id IS NULL
GROUP BY t.name, t.tovar_id, p.path
HAVING COUNT(knotConnections.tovar_id) = :yourNumberHere
    Login or Signup to reply.
  2. 0

    Rob Eyre’s answer shows the usual ways of doing this, but what you had was close to working, you just need to change

    COUNT(SELECT *

    to

    (SELECT COUNT(*)

    When you want to use the value of a subquery it in an expression, you just have to enclose it in parens (and make sure it is returning the single value you want, not more than one value or more than one row).

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search