CODEWITHSUNDEEP
mysqlsql
Ali Raza
Ali Raza

Reputation: 489

get data from four tables using one signle query

I have four tables. First let me share their structure

Sub category

id
name

Sub Category three

id
name
sub_category_id

products

id
name
sub_category_three_id

images

 id
 image
 product_id

Now I want to get 1 sub_category, and on the base of sub_category(id) get all data from sub category three, and on the base of sub_category_three all the products of sub_category_three, and all the images on the base of products.

I tried this query on three tables but then it gets complicated so I came here.

SELECT sct.sct_name,sc_name,p.name 
  FROM sub_categories_three sct 
 INNER JOIN sub_categories sc 
    ON sc.id = sct.sub_category_id 
 INNER JOIN products p 
    ON sct.id = p.sub_category_three

Upvotes: 3

Views: 207

Answers (3)

ThS
ThS

Reputation: 4783

You could use the following query, it contains some explanations : 

SELECT
    -- used aliases as most of the tables have same field names.
    -- not required but it will help us distinguish between the returned values.
    -- also the query will work without aliases as we did make aliases for table names which is required to prevent ambiguous field names. 
    `sc`.`id` AS `scID`,
    `sc`.`name` AS `scName`,
    `sct`.`id` AS `sctID`,
    `sct`.`name` AS `sctName`,
    `sct`.`sub_category_id`,
    `p`.`id` AS `pID`,
    `p`.`name` AS `pName`,
    `p`.`sub_category_three_id`,
    `i`.`id` AS `iID`,
    `i`.`image`,
    `i`.`product_id`
FROM `sub_categories` `sc`
INNER JOIN `sub_categories_three` `sct` ON -- link `sub_categories` and `sub_categories_three` tables 
    `sct`.`sub_category_id` = `sc`.`id` -- based on `sub_categories`.`id` and `sub_categories_three`.`sub_category_id`.
INNER JOIN `products` `p` ON -- link `sub_categories_three` and `products` tables
    `p`.`sub_category_three_id` = `sct`.`id` -- based on `sub_categories_three`.`id` and `products`.`sub_category_three_id`.
INNER JOIN `images` `i` ON -- link `products` and `images` tables
    `i`.`product_id` = `p`.`id` -- based on `products`.`id` and `images`.`product_id`.
WHERE `sc`.`id` = 1 -- a WHERE clause is needed, for demo purpuses I fetched only the row having `id` = 1 from `sub_categories` table. Change it per your requirements.

Hope that helps, feel free to ask for more clarifications.

Upvotes: 3

Arun pandian M
Arun pandian M

Reputation: 882

This is simple inner join query 

SELECT * FROM products 
INNER JOIN images ON images.product_id = products.id                           //link images with product id 
INNER JOIN sub category three sct ON sct.id = products.subcategory_three_id    //link product subcategory three id with sub category three 
INNER JOIN sub category sc ON sc.id = sct.sub_category_id                     //finally link sub category with subcategory three `

Add Where condition if neccessary

EDIT

SELECT * FROM products 
INNER JOIN images ON images.product_id = products.id                           //link images with product id 
INNER JOIN sub category three sct ON sct.id = products.subcategory_three_id    //link product subcategory three id with sub category three 
INNER JOIN sub category sc ON sc.id = 
  (  SELECT id
       FROM sub category AS sc
        WHERE sc.id = sct.sub_category_id
        LIMIT 1

   )             //finally link sub category with subcategory three `

Upvotes: 2

Nir Levy
Nir Levy

Reputation: 12953

This is a simple join query, to connect all tables:

select * from sub_category sc inner join
  sub_category_three sct on sc.id = sct.sub_category_id inner join
  products p on sct.id = p.sub_category_three_id inner join 
  images i on i.product_id = p.id
  where ....

Upvotes: 2

Related Questions