postgresql combine 2 unrelated tables
I have 2 tables: alarms and cctvs.
Alarms table consist of: alarm_ID and date.
CCTVs table also consist of: cctv_ID and date.
I would like to combine both tables such that it results in a table with three columns: cctv_ID, alarm_ID and date. cctv_ID value would be null if it has an alarm_ID and alarm_ID would be null if it has a cctv_ID.
How do I combine these 2 tables together?
alarm table:
alarm_ID | Date
1 | 1/1/10
cctv table:
cctv_ID | Date
1 | 2/1/10
final table should look like:
alarm_ID | CCTV_ID | Date
1 | null | 1/1/10
null | 1 | 2/1/10
I have this code currently:
with alarms as(
select alarm_id, last_maintenance_date
from REMOTE_SECURITY.alarms
where last_maintenance_date < '4/4/18'),
cctvs as (
select cctv_id, last_maintenance_date
from REMOTE_SECURITY.cctvs
where last_maintenance_date <'4/4/18')
select * from alarms
union
select * from cctvs
;
But this code gives me just the alarm_id column and the date column and it merges the cctv_id in the same column so I dont know which is an alarm and which is a cctv!
Any help would be much appreciated!
sql postgresql
edited Nov 23 '18 at 4:47
melpomene
60.4k54793
asked Nov 23 '18 at 4:39
AmorosoAmoroso
1571212
add a comment |
I have 2 tables: alarms and cctvs.
Alarms table consist of: alarm_ID and date.
CCTVs table also consist of: cctv_ID and date.
I would like to combine both tables such that it results in a table with three columns: cctv_ID, alarm_ID and date. cctv_ID value would be null if it has an alarm_ID and alarm_ID would be null if it has a cctv_ID.
How do I combine these 2 tables together?
alarm table:
alarm_ID | Date
1 | 1/1/10
cctv table:
cctv_ID | Date
1 | 2/1/10
final table should look like:
alarm_ID | CCTV_ID | Date
1 | null | 1/1/10
null | 1 | 2/1/10
I have this code currently:
with alarms as(
select alarm_id, last_maintenance_date
from REMOTE_SECURITY.alarms
where last_maintenance_date < '4/4/18'),
cctvs as (
select cctv_id, last_maintenance_date
from REMOTE_SECURITY.cctvs
where last_maintenance_date <'4/4/18')
select * from alarms
union
select * from cctvs
;
But this code gives me just the alarm_id column and the date column and it merges the cctv_id in the same column so I dont know which is an alarm and which is a cctv!
Any help would be much appreciated!
sql postgresql
edited Nov 23 '18 at 4:47
melpomene
60.4k54793
asked Nov 23 '18 at 4:39
AmorosoAmoroso
1571212
add a comment |
I have 2 tables: alarms and cctvs.
Alarms table consist of: alarm_ID and date.
CCTVs table also consist of: cctv_ID and date.
I would like to combine both tables such that it results in a table with three columns: cctv_ID, alarm_ID and date. cctv_ID value would be null if it has an alarm_ID and alarm_ID would be null if it has a cctv_ID.
How do I combine these 2 tables together?
alarm table:
alarm_ID | Date
1 | 1/1/10
cctv table:
cctv_ID | Date
1 | 2/1/10
final table should look like:
alarm_ID | CCTV_ID | Date
1 | null | 1/1/10
null | 1 | 2/1/10
I have this code currently:
with alarms as(
select alarm_id, last_maintenance_date
from REMOTE_SECURITY.alarms
where last_maintenance_date < '4/4/18'),
cctvs as (
select cctv_id, last_maintenance_date
from REMOTE_SECURITY.cctvs
where last_maintenance_date <'4/4/18')
select * from alarms
union
select * from cctvs
;
But this code gives me just the alarm_id column and the date column and it merges the cctv_id in the same column so I dont know which is an alarm and which is a cctv!
Any help would be much appreciated!
sql postgresql
edited Nov 23 '18 at 4:47
melpomene
60.4k54793
asked Nov 23 '18 at 4:39
AmorosoAmoroso
1571212
I have 2 tables: alarms and cctvs.
Alarms table consist of: alarm_ID and date.
CCTVs table also consist of: cctv_ID and date.
I would like to combine both tables such that it results in a table with three columns: cctv_ID, alarm_ID and date. cctv_ID value would be null if it has an alarm_ID and alarm_ID would be null if it has a cctv_ID.
How do I combine these 2 tables together?
alarm table:
alarm_ID | Date
1 | 1/1/10
cctv table:
cctv_ID | Date
1 | 2/1/10
final table should look like:
alarm_ID | CCTV_ID | Date
1 | null | 1/1/10
null | 1 | 2/1/10
I have this code currently:
with alarms as(
select alarm_id, last_maintenance_date
from REMOTE_SECURITY.alarms
where last_maintenance_date < '4/4/18'),
cctvs as (
select cctv_id, last_maintenance_date
from REMOTE_SECURITY.cctvs
where last_maintenance_date <'4/4/18')
select * from alarms
union
select * from cctvs
;
But this code gives me just the alarm_id column and the date column and it merges the cctv_id in the same column so I dont know which is an alarm and which is a cctv!
Any help would be much appreciated!
sql postgresql
sql postgresql
edited Nov 23 '18 at 4:47
melpomene
60.4k54793
asked Nov 23 '18 at 4:39
AmorosoAmoroso
1571212
edited Nov 23 '18 at 4:47
melpomene
60.4k54793
asked Nov 23 '18 at 4:39
AmorosoAmoroso
1571212
edited Nov 23 '18 at 4:47
melpomene
60.4k54793
edited Nov 23 '18 at 4:47
melpomene
60.4k54793
edited Nov 23 '18 at 4:47
melpomene
60.4k54793
60.4k54793
asked Nov 23 '18 at 4:39
AmorosoAmoroso
1571212
asked Nov 23 '18 at 4:39
AmorosoAmoroso
1571212
asked Nov 23 '18 at 4:39
AmorosoAmoroso
1571212
1571212
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
You can use such a logic by contribution of case..when statement :
select (case when col0 = 'a' then 1 end) as alarm_ID,
(case when col0 = 'c' then 1 end) as CCTV_ID,
q.last_maintenance_date as Date
from
(with alarms(alarm_ID,last_maintenance_date) as
(select 1, date'2010-01-01'),
cctvs(cctv_ID,last_maintenance_date) as
(select 1, date'2010-01-02')
select 'a' as col0,a.* from alarms a
union all
select 'c' as col0,c.* from cctvs c
) q;
Rextester Demo
answered Nov 23 '18 at 5:31
Barbaros ÖzhanBarbaros Özhan
13.3k71633
add a comment |
Use UNION
select alarm_id,'' AS cctv_id, last_maintenance_date
from REMOTE_SECURITY.alarms
where last_maintenance_date <'4/4/18')
UNION
select '' AS alarm_id,cctv_id, last_maintenance_date
from REMOTE_SECURITY.cctvs
where last_maintenance_date <'4/4/18')
answered Nov 23 '18 at 5:23
LuvLuv
9,255154061
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53440724%2fpostgresql-combine-2-unrelated-tables%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
You can use such a logic by contribution of case..when statement :
select (case when col0 = 'a' then 1 end) as alarm_ID,
(case when col0 = 'c' then 1 end) as CCTV_ID,
q.last_maintenance_date as Date
from
(with alarms(alarm_ID,last_maintenance_date) as
(select 1, date'2010-01-01'),
cctvs(cctv_ID,last_maintenance_date) as
(select 1, date'2010-01-02')
select 'a' as col0,a.* from alarms a
union all
select 'c' as col0,c.* from cctvs c
) q;
Rextester Demo
answered Nov 23 '18 at 5:31
Barbaros ÖzhanBarbaros Özhan
13.3k71633
add a comment |
You can use such a logic by contribution of case..when statement :
select (case when col0 = 'a' then 1 end) as alarm_ID,
(case when col0 = 'c' then 1 end) as CCTV_ID,
q.last_maintenance_date as Date
from
(with alarms(alarm_ID,last_maintenance_date) as
(select 1, date'2010-01-01'),
cctvs(cctv_ID,last_maintenance_date) as
(select 1, date'2010-01-02')
select 'a' as col0,a.* from alarms a
union all
select 'c' as col0,c.* from cctvs c
) q;
Rextester Demo
answered Nov 23 '18 at 5:31
Barbaros ÖzhanBarbaros Özhan
13.3k71633
add a comment |
You can use such a logic by contribution of case..when statement :
select (case when col0 = 'a' then 1 end) as alarm_ID,
(case when col0 = 'c' then 1 end) as CCTV_ID,
q.last_maintenance_date as Date
from
(with alarms(alarm_ID,last_maintenance_date) as
(select 1, date'2010-01-01'),
cctvs(cctv_ID,last_maintenance_date) as
(select 1, date'2010-01-02')
select 'a' as col0,a.* from alarms a
union all
select 'c' as col0,c.* from cctvs c
) q;
Rextester Demo
answered Nov 23 '18 at 5:31
Barbaros ÖzhanBarbaros Özhan
13.3k71633
You can use such a logic by contribution of case..when statement :
select (case when col0 = 'a' then 1 end) as alarm_ID,
(case when col0 = 'c' then 1 end) as CCTV_ID,
q.last_maintenance_date as Date
from
(with alarms(alarm_ID,last_maintenance_date) as
(select 1, date'2010-01-01'),
cctvs(cctv_ID,last_maintenance_date) as
(select 1, date'2010-01-02')
select 'a' as col0,a.* from alarms a
union all
select 'c' as col0,c.* from cctvs c
) q;
Rextester Demo
answered Nov 23 '18 at 5:31
Barbaros ÖzhanBarbaros Özhan
13.3k71633
answered Nov 23 '18 at 5:31
Barbaros ÖzhanBarbaros Özhan
13.3k71633
answered Nov 23 '18 at 5:31
Barbaros ÖzhanBarbaros Özhan
13.3k71633
answered Nov 23 '18 at 5:31
Barbaros ÖzhanBarbaros Özhan
13.3k71633
13.3k71633
add a comment |
add a comment |
Use UNION
select alarm_id,'' AS cctv_id, last_maintenance_date
from REMOTE_SECURITY.alarms
where last_maintenance_date <'4/4/18')
UNION
select '' AS alarm_id,cctv_id, last_maintenance_date
from REMOTE_SECURITY.cctvs
where last_maintenance_date <'4/4/18')
answered Nov 23 '18 at 5:23
LuvLuv
9,255154061
add a comment |
Use UNION
select alarm_id,'' AS cctv_id, last_maintenance_date
from REMOTE_SECURITY.alarms
where last_maintenance_date <'4/4/18')
UNION
select '' AS alarm_id,cctv_id, last_maintenance_date
from REMOTE_SECURITY.cctvs
where last_maintenance_date <'4/4/18')
answered Nov 23 '18 at 5:23
LuvLuv
9,255154061
add a comment |
Use UNION
select alarm_id,'' AS cctv_id, last_maintenance_date
from REMOTE_SECURITY.alarms
where last_maintenance_date <'4/4/18')
UNION
select '' AS alarm_id,cctv_id, last_maintenance_date
from REMOTE_SECURITY.cctvs
where last_maintenance_date <'4/4/18')
answered Nov 23 '18 at 5:23
LuvLuv
9,255154061
Use UNION
select alarm_id,'' AS cctv_id, last_maintenance_date
from REMOTE_SECURITY.alarms
where last_maintenance_date <'4/4/18')
UNION
select '' AS alarm_id,cctv_id, last_maintenance_date
from REMOTE_SECURITY.cctvs
where last_maintenance_date <'4/4/18')
answered Nov 23 '18 at 5:23
LuvLuv
9,255154061
answered Nov 23 '18 at 5:23
LuvLuv
9,255154061
answered Nov 23 '18 at 5:23
LuvLuv
9,255154061
answered Nov 23 '18 at 5:23
LuvLuv
9,255154061
9,255154061
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53440724%2fpostgresql-combine-2-unrelated-tables%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
