I have two tables:

Table 1: Category Table

Table 2: Product Table

I want to ask if it is possible to make a sql result where Table 1 contains the Category Code, Category Description, and Total but in the next column contains Table 2 which contains the Product Code, Product Name, and Quantity?

Expected outcome:

CodePudding user response：

1. Try this (tested on dbfiddle)

SELECT catcode as code, catdesc as name, total as amount
FROM (
  SELECT catcode, catdesc, total, catcode AS catcode2 FROM category
  UNION ALL
  SELECT prodcode, proddesc, amount, catcode AS catcode2 FROM product
) tmp
ORDER BY catcode2, catcode;