Postgresql select from 2 tables. Joins?
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 | | jqeron@wqwerweper.com
0 | | qwerox@wqwekeeper.com
0 | | erreon@fdfdeper.com
0 | | sfar@weasdfer.com
0 | | rawq@gdfefdgheper.com
1 | 15555555555 |
1 | 15555551806 |
1 | 15555555508 |
1 | 15055555506 |
1 | 15055555558 |
1 | | rfoasdfx@wefdaser.com
1 | | radfy@wfdfder.com
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?
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
精彩评论