CODEWITHSUNDEEP
sqlitejoinhavingsql-in
Laurent
Laurent

Reputation: 31

Sqlite SELECT with multiple conditions

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 :)

Upvotes: 1

Views: 5683

Answers (2)

forpas
forpas

Reputation: 164139

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

Upvotes: 1

MSalters
MSalters

Reputation: 179981

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.

Upvotes: 1

Related Questions