SQL query where all A's must have the following coumns from B that are equal?

Question

Here's my specific example of what I mean. I have the following table defined:

CREATE TABLE pets (
    id INTEGER UNSIGNED NOT NULL,
    name VARCHAR(20) NOT NULL,
    breed VARCHAR(20) NOT NULL,
    color VARCHAR(20) NOT NULL,
    weight INTEGER UNSIGNED NOT NULL,
    PRIMARY KEY (id)
);

How would I write an SQL query to find the breeds that include pets of all the same colors as the colors of cocker spaniels? So if all cocker spaniels in the database are either black or white, I need to find all the distinct breeds with at least 1 black pet and 1 yellow pet. I've been stumped with this one for a while and haven't been able to find a comparable answer online so any help here would be appreciated.

give some sample data and the output sample how you want? – Kumar_2002 Oct 17 '12 at 01:05 — Kumar_2002, Oct 17 '12 at 01:05
Yellow is the new white? – Erwin Brandstetter Oct 17 '12 at 01:11 — Erwin Brandstetter, Oct 17 '12 at 01:11

score 2 · Answer 1 · edited May 23 '17 at 11:56

As always, keeping your actual RDBMS a secret made it harder.

For databases supporting CTE (PostgreSQL, Oracle, SQL Server, ..)

CTEs:

WITH colors AS (
    SELECT DISTINCT color
    FROM   pets
    WHERE  breed = 'cocker spaniels'
    )
SELECT p.breed
FROM   colors c
JOIN   pets   p USING (color)
GROUP  BY p.breed
HAVING count(DISTINCT p.color) = (SELECT count(*) FROM color);

Explain

Get set of distinct colors of cocker spaniels in the CTE color.
JOIN all pets with the same color, group pets by breed
In the HAVING clause verify that the number of distinct colors match the number of colors that cocker spaniels come in.

Ergo: The resulting pets (including cocker spaniels) come in all the colors that cocker spaniels do - and possibly more.

This is a special case of relational division. We assembled a whole arsenal of queries under this related question recently.

For MySQL

SELECT p.breed
FROM (
   SELECT DISTINCT color
   FROM   pets
   WHERE  breed = 'cocker spaniels'
   ) c
JOIN   pets p USING (color)
GROUP  BY p.breed
HAVING count(DISTINCT p.color) = (
   SELECT count(DISTINCT color)
   FROM   pets
   WHERE  breed = 'cocker spaniels'
   );

-> sqlfiddle

Whoops, I forgot to specify that I'm using MySQL, which does not support WITH :/ How would I do this without CTEs? — A_Obregon, Oct 17 '12 at 03:32

score 0 · Answer 2 · answered Oct 17 '12 at 01:11

0

SELECT  DISTINCT breeds  FROM pets 
WHERE color in (SELECT color FROM pets where name='cocker spaniels')

answered Oct 17 '12 at 01:11

Kumar_2002

584
1
5
14

score 0 · Answer 3 · answered Oct 17 '12 at 01:25

If I understand you correctly, you don't want breeds where the breed is all white or all black. You only want breeds that are white AND black (the different colors of the cocker spaniels). Based on that, here's what I came up with (though it probably isn't the most efficient).

DECLARE @pets TABLE(
  id INTEGER NOT NULL,
  name VARCHAR(20) NOT NULL,
  breed VARCHAR(20) NOT NULL,
  color VARCHAR(20) NOT NULL,
  weight INTEGER NOT NULL,
  PRIMARY KEY (id)
);

INSERT INTO @pets VALUES (1, 'jeff', 'breed1', 'black', 20)
INSERT INTO @pets VALUES (2, 'larry', 'breed1', 'white', 20)
INSERT INTO @pets VALUES (3, 'bob', 'breed2', 'black', 30)
INSERT INTO @pets VALUES (4, 'kevin', 'breed3', 'black', 30)
INSERT INTO @pets VALUES (5, 'jerry', 'breed3', 'white', 30)

DECLARE @breed VARCHAR(50)
SET @breed = 'breed1'
;WITH cte (color) AS (SELECT DISTINCT color FROM @pets WHERE breed = @breed)
SELECT breed, COUNT(id) FROM @pets WHERE color IN (SELECT color FROM cte)
  AND breed != @breed
GROUP BY breed
HAVING COUNT(id) >= (SELECT COUNT(*) FROM cte)

Result: breed3

If you're using a version of SQL that doesn't support CTEs, you could use a temp table or table variable to hold your colors that you need to look for in other breeds. Hopefully that helps!

score 0 · Answer 4 · answered Oct 17 '12 at 01:46

--parameter
DECLARE @breed varchar(20)
SET @breed = 'Cocker Spaniel' 

--count distinct colors for param
DECLARE @colors int
SELECT @colors = COUNT(DISTINCT color) FROM pets WHERE breed = @breed 

--match param breed with others based on color
--return only breed with matching count of distinct colors
SELECT otherBreeds.breed
FROM
    (SELECT DISTINCT breed, color FROM pets WHERE breed = @breed) AS thisBreed
    LEFT OUTER JOIN (SELECT DISTINCT breed, color FROM pets WHERE breed <> @breed) AS otherBreeds
    ON thisBreed.color = otherBreeds.color
GROUP BY otherBreeds.breed
HAVING COUNT(DISTINCT otherBreeds.color) = @colors

SQL query where all A's must have the following coumns from B that are equal?

4 Answers4

For databases supporting CTE (PostgreSQL, Oracle, SQL Server, ..)

Explain

For MySQL