Question 252012 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – SQL select from two tables with union dont get right column name

I want to select data from two tables, category and sub_category.

But I only get the column names of category. I want both category and sub_category.

Here is the result:

  { category_id: 3, category: 'GARDEN' },
  { category_id: 6, category: 'ART' },
  { category_id: 4, category: 'CONSOLE' },
  { category_id: 2, category: 'ELECTRONICS' },
  { category_id: 4, category: 'ELECTRONICS' },
  { category_id: 2, category: 'CONSOLE' },
  { category_id: 5, category: 'CHAIR' },
  { category_id: 6, category: 'COLORS' },
  { category_id: 5, category: 'GARDEN' },
  { category_id: 1, category: 'CONSOLE' },
  { category_id: 3, category: 'CUT' },
  { category_id: 1, category: 'ELECTRONICS' }

Here is my code:

SELECT
    c.id AS category_id,
    c.category
FROM 
    category c

UNION

SELECT 
    sc.id AS sub_category_id,
    sc.sub_category
FROM 
    sub_category sc

€:

here is my expected result:

  { category_id: 3, category: 'GARDEN' },
  { category_id: 6, category: 'ART' },
  { category_id: 4, category: 'CONSOLE' },
  { category_id: 2, category: 'ELECTRONICS' },
  { category_id: 4, category: 'ELECTRONICS' },
  { category_id: 2, category: 'CONSOLE' },
  { category_id: 5, category: 'CHAIR' },
  { category_id: 6, category: 'COLORS' },
  { sub_category_id: 5, category: 'GARDEN' },
  { sub_category_id: 1, category: 'CONSOLE' },
  { sub_category_id: 3, category: 'CUT' },
  { sub_category_id: 1, category: 'ELECTRONICS' }
Tags:

2

Answers


  1. 0

    The problem is that you are using a UNION operation, which combines the results of two queries vertically, but it discards the column names from the second query and renames the columns based on the first query.

    You should use a JOIN operation instead!

    SELECT
    c.id as category_id,
    c.category,
    sc.id as sub_category_id,
    sc.sub_category
FROM
    category c
JOIN
    sub_category sc ON c.id = sc.category_id;
    Login or Signup to reply.
  2. 0

    If you want to be able to differentiate what table the rows came from in a UNION statement, you can e.g. add a separate column to your result set to specify that – something like this:

    SELECT
    c.id AS category_id,
    c.category,
    'Category' AS source_table
FROM 
    category c

UNION

SELECT 
    sc.id,
    sc.sub_category,
    'Subcategory' AS source_table
FROM 
    sub_category sc

    Then the result you get will contain either Category or Subcategory in the new source_table column to tell you where the row came from.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search