CODEWITHSUNDEEP
mysql
maaudet
maaudet

Reputation: 2358

MySQL select with categories

I have 3 tables:

section
=======
id

cat
===
id
section_id

product
=======
id
cat_id

and I would like to select all the products matching a certain section. Is there a way to do this in a single query?

Upvotes: 0

Views: 105

Answers (3)

Dubas
Dubas

Reputation: 2876

To select all products from a single section Id:

SELECT 
    * 
FROM 
    product INNER JOIN cat ON cat.id=product.cat_id
WHERE
    cat.section_id = <section id to filter>

Example for section id 4:

SELECT 
    * 
FROM 
    product INNER JOIN cat ON cat.id=product.cat_id
WHERE
    cat.section_id = 4

Also you can search all the items that complains two or more ids, For example searching items with section 1,4 and 7 :

SELECT 
    * 
FROM 
    product INNER JOIN cat ON cat.id=product.cat_id
WHERE
    cat.section_id in (1,4,7)

To select all products with certaing section using "other" section field than section id

SELECT 
    * 
FROM 
   product
   INNER JOIN cat ON cat.id=product.cat_id
   INNER JOIN section ON section.id=cat.section_id
WHERE
    section.<other field> = <the field value>

Example with a section field (for example) "name" and searching the value "Section name 1"

SELECT 
    * 
FROM 
   product
   INNER JOIN cat ON cat.id=product.cat_id
   INNER JOIN section ON section.id=cat.section_id
WHERE
    section.name = 'Section name 1'

Also is really recommended to replace the "*" and get only the fields that you really require. This helps to fetch only the data that you needed.

For example if you only need the product "id" and a "ficticius" field named "name" from product you should do: (for the first example query)

SELECT 
    product.id as productId, product.name as productName
FROM 
    product INNER JOIN cat ON cat.id=product.cat_id
WHERE
    cat.section_id = <section id to filter>

Upvotes: 0

Tom van der Woerdt
Tom van der Woerdt

Reputation: 29985

Get all data related to the section :

SELECT * FROM product
INNER JOIN cat ON cat.id=product.cat_id
INNER JOIN section ON section.id=cat.section_id
WHERE section.id = 5

Slightly faster solution which will ignore the section table altogether.

SELECT * FROM product
INNER JOIN cat ON cat.id=product.cat_id
WHERE cat.section_id = 5

Upvotes: 3

HJW
HJW

Reputation: 23443

Select * from product a left join cat b on (b.id=cat_id) left join section using (id);

Upvotes: 0

Related Questions