CODEWITHSUNDEEP
sqlpostgresql
Guilherme Rodriguez
Guilherme Rodriguez

Reputation: 194

PostgreSQL - Merge two selects queries

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

Upvotes: 2

Views: 3240

Answers (3)

dnoeth
dnoeth

Reputation: 60462

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

Upvotes: 2

FuzzyTree
FuzzyTree

Reputation: 32392

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 *

Upvotes: 1

BadZen
BadZen

Reputation: 4265

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)
  ...

Upvotes: 1

Related Questions