PostgreSQL - Merge two selects queries

Question

I have two tables :

company and users

the users table:

    id   |   name   |   cpf     |   phone_number  | company_id

    1    |   Jonh   | 111.11.11 |   1111-1111     |    1

    2    |   Marie  | 222.22.22 |   2222-2222     | 

    3    |   Paul   | 333.33.33 |   3333-3333     |    3

    4    |   Luna   | 444.44.44 |   4444-4444     |    1

    5    |   Leo    | 555.55.55 |   5555-5555     |

the company table:

id   |   name       |   cnpj      |   phone_number | company_data | consumer

1    |  companyA    | 111.1111.11 |   1111-1111    |     data1    | true

2    |  companyB    | 222.2222.22 |   2222-2222    |     data2    | true

3    |  companyC    | 333.3333.33 |   3333-3333    |     data3    | false

I want to select all the users where company_id IS NULL and all the companies where consumer is true

What I'm trying to do is something like this :

 Select u.name as name,
          u.cpf as document,
          u.phone_number as phoneNumber,
          'false' as company
          FROM users u 
          WHERE company_id is NULL

   UNION

   Select c.name as name,
          c.cnpj as document, 
          c.phone_number as phoneNumber,
          c.company_data as companyData
          'true' as company
          FROM company c
          WHERE c.consumer = 'true'

  ORDER BY id

And the answer I want is :

id   |   name       |   document  |   phone_number  | companyData |  company

1    |  companyA    | 111.1111.11 |   1111-1111     |   data 1    |   true

2    |  companyB    | 222.2222.22 |   2222-2222     |   data 2    |   true

2    |   Marie      | 222.22.22   |   2222-2222     |             |  false

5    |   Leo        | 555.55.55   |   5555-5555     |             |  false

I can accept a answer with the columns cpf and cnpj separed and the results null if it doens't apply to the selected entity. In this way I would not need the company column

Can you share some sample data and the result you're expecting? It's quite hard to understand the question as it's currently worded (for me, at least) — Mureinik
– Mureinik, Commented May 4, 2015 at 20:51
You're almost doing it right. You need to actually join the two tables in your FROM clause in the first code snip, and CASE each column off from there, not delegate to two different subselects. — BadZen
– BadZen, Commented May 4, 2015 at 20:55

dnoeth · Accepted Answer · 2015-05-04 21:55:00Z

2

You need the same number of columns for both selects, simply add a NULL column (you might have to cast it to a datatype):

   Select u.name as name,
          u.cpf as document,
          u.phone_number as phoneNumber,
          CAST(NULL AS VARCHAR(20)) as companyData,
          'false' as company
          FROM users u 
          WHERE company_id is NULL

   UNION

   Select c.name as name,
          c.cnpj as document, 
          c.phone_number as phoneNumber,
          c.company_data as companyData
          'true' as company
          FROM company c
          WHERE c.consumer = 'true'

  ORDER BY id

answered May 4, 2015 at 21:55

dnoeth

60.5k4 gold badges45 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Guilherme Rodriguez Over a year ago

Thanks, it works too ! I accepted the other because he answered first. I would upvote this but i can't yet =/

Vivek S. Over a year ago

@GuilhermeRodriguez I did it for you :) !!

BadZen · Accepted Answer · 2015-05-04 20:58:42Z

1

Per the above...

Select CASE WHEN users.company_id is NULL THEN users.name ELSE company.name ...
  ... FROM users JOIN company ON(users.company_id = company.id)
  ...

answered May 4, 2015 at 20:58

BadZen

4,3873 gold badges30 silver badges54 bronze badges

1 Comment

Guilherme Rodriguez Over a year ago

tried it but it ignored all the company data. Dont know why

FuzzyTree · Accepted Answer · 2015-05-04 21:52:41Z

1

If users and company have the same columns you can use a union query

select u.name, null from users where company_id is null
union
select c.name, c.some_column from users u 
join company c on u.company_id = c.id

If they don't have the same columns, you'll have to specify the column names you want to select manually instead of selecting *

edited May 4, 2015 at 21:52

answered May 4, 2015 at 21:07

FuzzyTree

32.4k3 gold badges58 silver badges87 bronze badges

3 Comments

Guilherme Rodriguez Over a year ago

I want something like that. The problem is the "company" have more columns and i get the error : ERROR: each UNION query must have the same number of columns. I want that the columns that does not appear in users be null

FuzzyTree Over a year ago

Then you have to explicitly select the columns you want and select "null" for the columns that are not in user. See update

Guilherme Rodriguez Over a year ago

It worked. My problem was that I was doing the selects in a diferent order. The union clause requires that all the data be on the same order. I thought that the alias would do that job. Thanks for the help!

Collectives™ on Stack Overflow

PostgreSQL - Merge two selects queries

3 Answers 3

2 Comments

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related