Create a table containing all rows of 3 tables join with 3 similar columns [SQL]-CodePudding

As the title indicates (in an incorrect way), I would like to create a table bringing together all the lines of 3 different tables, with as common points, their creation date and a folder id (present in each of the 3 tables).

simple schema of the DB : image of the DB schema with relations

How would you write this in SQL ?

PS: it's maybe super easy but I'm all new with SQL, sorry !

PS2: don't hesitate if you need more explanations or precisions

Thank you :)

What I tried :


SELECT product_folder.id AS product_folder_id
pens.brand AS pen_brand
schoolbags.size AS schoolbag_size
notebooks.number_of_pages AS notebook_pages
created_at,
updated_at
FROM product_folders pf
LEFT JOIN product_folders pf ON schoolbags.product_folder_id = pf.id
LEFT JOIN product_folders pf ON pens.product_folder_id = pf.id
LEFT JOIN product_folders pf ON notebooks.product_folder_id = pf.id

But it doesn’t return what I ask (an error).

EDIT :

The error : 'ERROR: table name "pf" specified more than once'

The table should look something like this : image of the table I want to have

More precisely : (*p_f_id = product_folder_id)

------------------------------all-products------------------------------

id p_f_id* brand      size    price  nb_of_pages  created_at  updated_at
-- ------- ---------- ------- ------ ------------ ----------- ---------
1  34      Watermark  NULL    34.00  NULL         12-04-2022  15-04-2022
2  22      NULL       medium  40.00  NULL         28-11-2022  29-11-2022
3  18      NULL       NULL    12.00  42           06-09-2022  06-09-2022

I know some of the columns will return NULL (ex: pens don't have pages so > NULL) and I'm ok with that. I just want the column product_folder_id filled with the id of the folder of the different products and the created_at and updated_at columns filled with the date of each rows.

CodePudding user response：

Your first error is that you joined multiple times the table product_folder with the same alias "pf", you cannot do this because if you want to select fields from any of the joined tables, SQL must know which one you want to select from (that's the goal of an alias). You could for example do:

SELECT pf1.id, pf2.id
FROM product_folders pf1
LEFT JOIN product_folders pf2 ON pf2.id = pf1.id

It makes no real sense (it will select twice the same id for each row of product_folders) but shows you that you must use the aliases in the SELECT part and in the ON part to distinguish joined tables.

Your main error though is for your case, you don't need to join on the same table, and then you don't need to give aliases. You want to join directly on the tables you want to select the fields from:

SELECT product_folder.id AS product_folder_id
pens.brand AS pen_brand
schoolbags.size AS schoolbag_size
notebooks.number_of_pages AS notebook_pages
/*created_at,
updated_at*/
FROM product_folders
LEFT JOIN schoolbags ON schoolbags.product_folder_id = product_folders.id
LEFT JOIN pens ON pens.product_folder_id = product_folders.id
LEFT JOIN notebooks ON notebooks.product_folder_id = product_folders.id

I commented created_at, updated_at because from the question we cannot know where you want these columns from, as they don't exist in product_folder. It could be schoolbags.created_at, schoolbags.updated_at or pens.created_at, pens.updated_at or notebooks.created_at, notebooks.updated_at, or all of them...

EDIT FOLLOWING MORE INFOS: Since you have entries in product_folder that don't correspond to the 3 joined tables, you must filter relevant results with a WHERE.

Additionnally, if each row in product_folder corresponds to only one of the 3 joined tables, you can retreive created_at / updated_at by testing if the id is null (or another field) with IF.

That would give:

SELECT product_folder.id AS product_folder_id,
pens.brand AS pen_brand,
schoolbags.size AS schoolbag_size,
notebooks.number_of_pages AS notebook_pages,
IF(pens.id IS NOT NULL, pens.created_at,
  IF(schoolbags.id IS NOT NULL, schoolbags.created_at, notebooks.created_at)
) as created_at,
IF(pens.id IS NOT NULL, pens.updated_at,
  IF(schoolbags.id IS NOT NULL, schoolbags.updated_at, notebooks.updated_at)
) as updated_at,
FROM product_folders
LEFT JOIN schoolbags ON schoolbags.product_folder_id = product_folders.id
LEFT JOIN pens ON pens.product_folder_id = product_folders.id
LEFT JOIN notebooks ON notebooks.product_folder_id = product_folders.id
WHERE (pens.brand IS NOT NULL OR schoolbags.size IS NOT NULL OR notebooks.number_of_pages IS NOT NULL)

CodePudding user response：

It is not known, what result you want. Some correction to your query may be help to next step

SELECT 
   pf.id AS product_folder_id
  ,pens.brand AS pen_brand
  ,schoolbags.size AS schoolbag_size
  ,notebooks.number_of_pages AS notebook_pages
  ,pf.created_at
  ,pf.updated_at
FROM product_folders pf
LEFT JOIN product_folders schoolbags ON schoolbags.product_folder_id = pf.id
LEFT JOIN product_folders pens ON pens.product_folder_id = pf.id
LEFT JOIN product_folders notebooks ON notebooks.product_folder_id = pf.id

May be table product_folders has name all_products, as shown on image. Try this

SELECT 
   pf.id AS product_folder_id
  ,pens.brand AS pen_brand
  ,schoolbags.size AS schoolbag_size
  ,notebooks.number_of_pages AS notebook_pages
  ,pf.created_at
  ,pf.updated_at
FROM all_products pf
LEFT JOIN all_products schoolbags ON schoolbags.product_folder_id = pf.id
LEFT JOIN all_products pens ON pens.product_folder_id = pf.id
LEFT JOIN all_products notebooks ON notebooks.product_folder_id = pf.id