1

I have to create a database with a PRODUCTS table and a CATEGORIES table. Each product has a name, a price, a creation date and may belong to several categories. Categories have a name and a flag to indicate whether the category is private or public. Then I have to select all records that belongs to more than 5 public categories.

I've created the tables like this:

CREATE TABLE PRODUCTS (
  ID_PROD int NOT NULL PRIMARY KEY,
  NAME TEXT(255),
  PRICE INTEGER,
  CREATION_DATE DATE
);

CREATE TABLE CATEGORIES (
  ID_CAT INTEGER NOT NULL PRIMARY KEY,
  NAME TEXT(255),
  PRIVATE INTEGER
);

CREATE TABLE PROD_CAT (
    ID INTEGER NOT NULL PRIMARY KEY,
    ID_PROD INTEGER,
    ID_CAT INTEGER,
    FOREIGN KEY (ID_PROD) REFERENCES PRODUCTS(ID_PROD),
    FOREIGN KEY (ID_CAT) REFERENCES CATEGORIES(ID_CAT)
)

I've managed to select all the records that belongs to more than 5 categories but I can't find out how to add the public category condition... Here's what I've tried:

This works:

SELECT NAME 
FROM PRODUCTS
WHERE ID_PROD IN (SELECT ID_PROD FROM PROD_CAT GROUP BY ID_PROD HAVING COUNT(*)>5)

But not this:

SELECT PRODUCTS.NAME 
FROM PRODUCTS, CATEGORIES
WHERE ID_PROD IN (SELECT ID_PROD FROM PROD_CAT GROUP BY ID_PROD HAVING COUNT(*)>5)
AND CATEGORIES.PRIVATE = 1

Any help would be appreciated :)

forpas
  • 160,666
  • 10
  • 38
  • 76
Laurent
  • 31
  • 1
  • 5

2 Answers2

1

You need a join of PROD_CAT to CATEGORIES:

SELECT NAME 
FROM PRODUCTS
WHERE ID_PROD IN (
  SELECT pc.ID_PROD 
  FROM PROD_CAT pc INNER JOIN CATEGORIES c
  ON c.ID_CAT = pc.ID_CAT
  WHERE c.PRIVATE -- or WHERE NOT c.PRIVATE for public categories
  GROUP BY pc.ID_PROD 
  HAVING COUNT(*) > 5
)

Or, without the operator IN, with joins of all 3 tables:

SELECT p.ID_PROD, p.NAME  
FROM PRODUCTS p
INNER JOIN PROD_CAT pc ON pc.ID_PROD = p.ID_PROD
INNER JOIN CATEGORIES c ON c.ID_CAT = pc.ID_CAT
WHERE c.PRIVATE -- or WHERE NOT c.PRIVATE for public categories
GROUP BY p.ID_PROD, p.NAME 
HAVING COUNT(*) > 5
forpas
  • 160,666
  • 10
  • 38
  • 76
1

Since this looks like homework, I'll give a good hint.

Your first query returns products belonging to more than 5 categories, using a sub-query for the COUNT. The restriction you added in the second query was added to the top-level WHERE-clause, not the sub-query. The sub-query still works on PROD_CAT and still returns the same results, which may include public categories.

MSalters
  • 173,980
  • 10
  • 155
  • 350