Sql Get Parent Where Children Have Specific Values

SQL get Parent where Children have specific values

You can do two joins and look for your specific records. This example shows that parent 1 will return with both kiddos, but not parent 2 that only has a Mike.

DECLARE @parent TABLE (ID INT)
DECLARE @child TABLE (ID INT, parentID INT, name VARCHAR(100))

INSERT INTO @parent 
VALUES 
(1),
(2),
(3),
(4),
(5),
(6)

INSERT INTO @child (ID, parentID, name)
VALUES 
(1, 1, 'Mike'),
(2, 1, 'John'),
(3, 2, 'Mike'),
(4, 2, 'Bill'),
(5, 3, 'Dave'),
(6, 4, 'Sam')

SELECT p.*

FROM @parent p

INNER JOIN @child c1
    ON c1.parentID = p.id
    AND c1.name = 'Mike'

INNER JOIN @child c2
    ON c2.parentID = p.ID
    AND c2.name = 'John'

Query to get parent where all the children have a certain value

Your query is on the right track in getting the results, it just needs a further filter to see there are no other records other than Test1 for the records you selected from CHILD.

We can do that using a NOT EXISTS condition

 select distinct p.* 
  from PARENT p 
   join CHILD c1
     on p.p_id = c1.p_id
  where c1.category = 'Test1'
    and not exists(select c2.id
                     from CHILD c2
                    where c2.p_id=c1.p_id 
                      and c2.category <> 'Test1')

Select all parents having a child with specific property

You can use exists:

select p.*
from parent p
where exists (select 1
              from child c
              where c.parent_id = p.id and c.property = ?
             );

Select parent records where ALL children records have a specific status

You can check that no not-closed record exists using NOT EXISTS query:

SELECT *
FROM TableA
WHERE NOT EXISTS (
    SELECT 1
    FROM TableB
    WHERE TableB.ParentID = TableA.ID
    AND TableB.Status <> 'closed'
)

SQL Query to select parent that has child records with different values in the same column

You need to leverage subqueries (or union) which flavor of sql are you using?

SELECT *
FROM CUSTOMER c
WHERE EXISTS (SELECT 1 FROM ORDERS o WHERE c.id = o.id and type = 'couch')
  and EXISTS (SELECT 1 FROM ORDERS o WHERE c.id = o.id and type = 'tv')

Find parents where all children have the same value

You can try grouping by the company name and retaining only those companies whose address is the same across all locations (this is what the inner query does):

WITH the_cte (companyName, locationName, address)
AS
(
    SELECT c.name, l.name, a.name
    FROM company AS c
    INNER JOIN location AS l
        ON l.company = c.id
    LEFT JOIN address AS a
        ON a.location = l.id
)

SELECT companyName, locationName, address
FROM the_cte
INNER JOIN
(
    SELECT companyName, COUNT(DISTINCT CASE WHEN address IS NULL THEN 0 ELSE 1 END)
    FROM the_cte
    GROUP BY companyName
    HAVING COUNT(DISTINCT CASE WHEN address IS NULL THEN 0 ELSE 1 END) = 1
) t
    ON the_cte.companyName = t.companyName

SQL: Get parent if and only if children have a certain property

Use a NOT EXISTS clause, like this:

SELECT id 
FROM asset A 
INNER JOIN resource R ON A.id = R.asset_id 
WHERE NOT EXISTS (
     /* Only return results if the asset has resources of all status = '1'  */
     SELECT 1 
     FROM Resources R2
     WHERE A.asset_id = R2.asset_id
     AND R2.status <> '1'
)

---

Related Topics