phpmyadmin Question posted in
The official documentation can be found here.

How to return rows from left table where condition on right table true on all row without sub query – Phpmyadmin

I have 2 table with this sample data.

Parent

id title
1 A
2 B
3 C

Childs

id p_id number
1 1 1
2 1 2
3 1 3
4 2 4
5 2 5
6 2 6
7 3 2
8 3 7
9 3 8
10 3 9

And I want to get rows from parents join with childs and number > 3.

But I want to receive only parent whose condition is correct on all childs, and even if the condition is not correct on one child, the parent should not be returned

And I want to do without subquery

SELECT * FROM `parent` 
LEFT JOIN `childs` on `childs`.`p_id` = `parent`.`id` 
WHERE `childs`.`number` > '3'

I want to get only parent B with this condition.

Thanks.

Tags:

3

Answers


  1. 0

    Maybe something like this can work

    SELECT *
FROM parent p, child c2
WHERE EXISTS ( SELECT * 
          FROM child c1 
          WHERE p.id = c1.p_id AND c1.number >3)  AND  p.id = c2.p_id AND c2.number <=3
    Login or Signup to reply.
  2. 0

    Try this:

    SELECT p.id, p.title, GROUP_CONCAT(number ORDER BY number) val
  FROM Parent p JOIN Childs c ON p.id=c.p_id
  GROUP BY p.id, p.title
  HAVING SUBSTRING_INDEX(val,',',1) > 3;

    Here’s a fiddle demo

    Login or Signup to reply.
  3. 0

    And I want to get rows from parents join with childs and number > 3.

    But I want to receive only parent whose condition is correct on all childs, and even if the condition is not correct on one child, the parent should not be returned

    I would recommend:

    SELECT p.id, p.title
FROM Parent p JOIN
     Childs c
     ON p.id = c.p_id
GROUP BY p.id, p.title
HAVING MIN(number) > 3;

    There is no reason at all to use strings for what you want to do. It just confuses the logic.

    An even more efficient method would use NOT EXISTS:

    select p.*
from p
where not exists (select 1
                  from childs c
                  where p.id = c.p_id and c.number <= 3
                 );
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search