Postgresql select from 2 tables. Joins?

Question

I have 2 tables that look like this:

    Table "public.phone_lists"
  Column  |       Type        |                             Modifiers                              
----------+-------------------+--------------------------------------------------------------------
 id       | integer           | not null default nextval(('"phone_lists_id_seq"'::text)::regclass)
 list_id  | integer           | not null
 sequence | integer           | not null
 phone    | character varying | 
 name     | character varying |

and

Table "public.email_lists"
 Column  |       Type        |                             Modifiers                              
---------+-------------------+--------------------------------------------------------------------
 id      | integer           | not null default nextval(('"email_lists_id_seq"'::text)::regclass)
 list_id | integer           | not null
 email   | character varying |

I'm trying to get the list_id, phone, and emails out of the tables in one table. I'm looking for an output like:

list_id |    phone    |             email              
---------+-------------+--------------------------------
       0 |             | [email protected]
       0 |             | [email protected]
       0 |             | [email protected]
       0 |             | [email protected]
       0 |             | [email protected]
       1 | 15555555555 | 
       1 | 15555551806 | 
       1 | 15555555508 | 
       1 | 15055555506 | 
       1 | 15055555558 | 
       1 |             | [email protected]
       1 |             | [email protected]

I've come up with

select pl.list_id, pl.phone, el.email from phone_lists as pl left join email_lists as el using (list_id);

but thats not quite right. Any suggestions?

You provided output but not input, please provide one. Also, list_id is NOT NULL in both tables, but you expect NULLs in your output. — Quassnoi
– Quassnoi, Commented Apr 28, 2010 at 15:37
Those should and do have a list_id of 0, but the formatting messed up. I'll fix that. — Daniel
– Daniel, Commented Apr 28, 2010 at 15:38

Quassnoi · Accepted Answer · 2010-04-28 15:41:59Z

1

SELECT  list_id, phone, email
FROM    (
        SELECT  list_id, NULL AS phone, email, 1 AS set_id
        FROM    email_lists
        UNION ALL
        SELECT  list_id, phone, NULL AS email, 2 AS set_id
        FROM    phone_lists
        ) q
ORDER BY
        list_id, set_id

answered Apr 28, 2010 at 15:41

Quassnoi

427k94 gold badges628 silver badges623 bronze badges

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

2 Comments

Daniel Over a year ago

could you be so kind as to explain how this works, and it does work. Thanks.

Quassnoi Over a year ago

@Daniel: UNION ALL is a multiset addition operation, it concatenates two recordsets together. Then the result of the concatenation is ordered by list id, then by source

Collectives™ on Stack Overflow

Postgresql select from 2 tables. Joins?

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related