Search code examples
mysqlsqlapi

How Can I join multiple tables with information i want?

I'm using mysql 8.0.31 version.

Below is my database schema ERD.

1

I want to get inforamtion about products from my DB.

Below is my sql raw query.

SELECT  
p.id, p.name, sc.name sub_category, c.name category, count(po.color_id) color_count
FROM products p
INNER JOIN sub_categories sc 
ON sc.id = p.sub_category_id 
INNER JOIN categories c 
ON sc.category_id = c.id 
LEFT JOIN products_options po 
ON po.product_id = p.id
INNER JOIN genders g 
ON po.gender_id = g.id 
WHERE c.id = 1
GROUP BY p.id
ORDER BY p.id ASC;

I want to join products table with sub_categories, categories to get categories information about my products AND products table with products_options, colors, genders to get color_count about a product and gender type about a product.

So I wanted to query like this added g.type (gender type information for a product)

SELECT  
p.id, p.name, sc.name sub_category, c.name category, count(po.color_id) color_count, g.type
FROM products p
INNER JOIN sub_categories sc 
ON sc.id = p.sub_category_id 
INNER JOIN categories c 
ON sc.category_id = c.id 
LEFT JOIN products_options po 
ON po.product_id = p.id
INNER JOIN genders g 
ON po.gender_id = g.id 
WHERE c.id = 1
GROUP BY p.id
ORDER BY p.id ASC;

But this occurs error like this

SQL Error [1055] [42000]: Expression #6 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'mydb.g.type' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

I think this error occurs cuz I didn't use aggregate function for g.type.

Below is my result except g.type in sql select field.

2

I want to get genders.type field together.

I think I need to sub query and join that table created by sub query. But I don' t know well How can I implement that method.

To combine this gender type information, How can I modify my sql query?

I tried multiple method sql queries in various ways to join table.

Solution

  • You need to either remove the column from select list like:

    SELECT p.id, count(po.color_id) color_count
FROM products p
INNER JOIN sub_categories sc 
ON sc.id = p.sub_category_i`enter code here`d 
INNER JOIN categories c 
ON sc.category_id = c.id 
LEFT JOIN products_options po 
ON po.product_id = p.id
INNER JOIN genders g 
ON po.gender_id = g.id 
WHERE c.id = 1
GROUP BY p.id
ORDER BY p.id ASC;

    or add in group by clause the select columns like:

    SELECT  p.id, p.name, sc.name sub_category, c.name category, count(po.color_id) color_count, g.type
FROM products p
INNER JOIN sub_categories sc 
ON sc.id = p.sub_category_id 
INNER JOIN categories c 
ON sc.category_id = c.id 
LEFT JOIN products_options po 
ON po.product_id = p.id
INNER JOIN genders g 
ON po.gender_id = g.id 
WHERE c.id = 1
GROUP BY p.id,p.name, sc.name sub_category, c.name category, g.type
ORDER BY p.id ASC;