How to use CASE or IF-statement in Postgres to select from different table?
0
I want to make a selection from one of many tables. This selection depends on some condition. How can I make it?
I suppose it should be some like this (but it doesn't work):
CASE x
WHEN x=1 THEN
select Id,Name from table1
WHEN x=2 THEN
select Id,Name from table2
WHEN x=3 THEN
select Id,Name from table3
END CASE;
sql postgresql case
edited Nov 25 '18 at 13:20
a_horse_with_no_name
303k46463559
asked Nov 25 '18 at 12:57
user2078264user2078264
52
|
show 2 more comments
0
I want to make a selection from one of many tables. This selection depends on some condition. How can I make it?
I suppose it should be some like this (but it doesn't work):
CASE x
WHEN x=1 THEN
select Id,Name from table1
WHEN x=2 THEN
select Id,Name from table2
WHEN x=3 THEN
select Id,Name from table3
END CASE;
sql postgresql case
edited Nov 25 '18 at 13:20
a_horse_with_no_name
303k46463559
asked Nov 25 '18 at 12:57
user2078264user2078264
52
Where doesxcome from?
– a_horse_with_no_name
Nov 25 '18 at 13:20
It should be as parameter for TFDQuery from delphi (or it is also can be as in-parameter stored procedure in database)
– user2078264
Nov 25 '18 at 13:23
Why can't you do theIFin Delphi? And why do you have three different tables storing the same information? From the example you have given, a single table with an additional columnxwould be better
– a_horse_with_no_name
Nov 25 '18 at 13:24
It's a bad practice - dynamic creation of SQL in runtime. Different tables has different information. Only 2 fields are common - Id and Name
– user2078264
Nov 25 '18 at 13:47
Not sure I agree on IF statements being bad in code for SELECT statements, the logic has to go somewhere, SQL or Code, it's better placed where you can see it for most cases
– hngr18
Nov 25 '18 at 13:56
|
show 2 more comments
0
0
0
I want to make a selection from one of many tables. This selection depends on some condition. How can I make it?
I suppose it should be some like this (but it doesn't work):
CASE x
WHEN x=1 THEN
select Id,Name from table1
WHEN x=2 THEN
select Id,Name from table2
WHEN x=3 THEN
select Id,Name from table3
END CASE;
sql postgresql case
edited Nov 25 '18 at 13:20
a_horse_with_no_name
303k46463559
asked Nov 25 '18 at 12:57
user2078264user2078264
52
I want to make a selection from one of many tables. This selection depends on some condition. How can I make it?
I suppose it should be some like this (but it doesn't work):
CASE x
WHEN x=1 THEN
select Id,Name from table1
WHEN x=2 THEN
select Id,Name from table2
WHEN x=3 THEN
select Id,Name from table3
END CASE;
sql postgresql case
sql postgresql case
edited Nov 25 '18 at 13:20
a_horse_with_no_name
303k46463559
asked Nov 25 '18 at 12:57
user2078264user2078264
52
edited Nov 25 '18 at 13:20
a_horse_with_no_name
303k46463559
asked Nov 25 '18 at 12:57
user2078264user2078264
52
edited Nov 25 '18 at 13:20
a_horse_with_no_name
303k46463559
edited Nov 25 '18 at 13:20
a_horse_with_no_name
303k46463559
edited Nov 25 '18 at 13:20
a_horse_with_no_name
303k46463559
303k46463559
asked Nov 25 '18 at 12:57
user2078264user2078264
52
asked Nov 25 '18 at 12:57
user2078264user2078264
52
asked Nov 25 '18 at 12:57
user2078264user2078264
52
52
Where doesxcome from?
– a_horse_with_no_name
Nov 25 '18 at 13:20
It should be as parameter for TFDQuery from delphi (or it is also can be as in-parameter stored procedure in database)
– user2078264
Nov 25 '18 at 13:23
Why can't you do theIFin Delphi? And why do you have three different tables storing the same information? From the example you have given, a single table with an additional columnxwould be better
– a_horse_with_no_name
Nov 25 '18 at 13:24
It's a bad practice - dynamic creation of SQL in runtime. Different tables has different information. Only 2 fields are common - Id and Name
– user2078264
Nov 25 '18 at 13:47
Not sure I agree on IF statements being bad in code for SELECT statements, the logic has to go somewhere, SQL or Code, it's better placed where you can see it for most cases
– hngr18
Nov 25 '18 at 13:56
|
show 2 more comments
Where doesxcome from?
– a_horse_with_no_name
Nov 25 '18 at 13:20
It should be as parameter for TFDQuery from delphi (or it is also can be as in-parameter stored procedure in database)
– user2078264
Nov 25 '18 at 13:23
Why can't you do theIFin Delphi? And why do you have three different tables storing the same information? From the example you have given, a single table with an additional columnxwould be better
– a_horse_with_no_name
Nov 25 '18 at 13:24
It's a bad practice - dynamic creation of SQL in runtime. Different tables has different information. Only 2 fields are common - Id and Name
– user2078264
Nov 25 '18 at 13:47
Not sure I agree on IF statements being bad in code for SELECT statements, the logic has to go somewhere, SQL or Code, it's better placed where you can see it for most cases
– hngr18
Nov 25 '18 at 13:56
Where does
x come from?– a_horse_with_no_name
Nov 25 '18 at 13:20
Where does
x come from?– a_horse_with_no_name
Nov 25 '18 at 13:20
It should be as parameter for TFDQuery from delphi (or it is also can be as in-parameter stored procedure in database)
– user2078264
Nov 25 '18 at 13:23
It should be as parameter for TFDQuery from delphi (or it is also can be as in-parameter stored procedure in database)
– user2078264
Nov 25 '18 at 13:23
Why can't you do the
IF in Delphi? And why do you have three different tables storing the same information? From the example you have given, a single table with an additional column x would be better– a_horse_with_no_name
Nov 25 '18 at 13:24
Why can't you do the
IF in Delphi? And why do you have three different tables storing the same information? From the example you have given, a single table with an additional column x would be better– a_horse_with_no_name
Nov 25 '18 at 13:24
It's a bad practice - dynamic creation of SQL in runtime. Different tables has different information. Only 2 fields are common - Id and Name
– user2078264
Nov 25 '18 at 13:47
It's a bad practice - dynamic creation of SQL in runtime. Different tables has different information. Only 2 fields are common - Id and Name
– user2078264
Nov 25 '18 at 13:47
Not sure I agree on IF statements being bad in code for SELECT statements, the logic has to go somewhere, SQL or Code, it's better placed where you can see it for most cases
– hngr18
Nov 25 '18 at 13:56
Not sure I agree on IF statements being bad in code for SELECT statements, the logic has to go somewhere, SQL or Code, it's better placed where you can see it for most cases
– hngr18
Nov 25 '18 at 13:56
|
show 2 more comments
2 Answers
2
active
oldest
votes
0
Inefficient solution the queries all 3 tables, but immitates a switch statement in code (assuming the retrieved columns are equivalent)
declare @inputValue int = 1
SELECT * FROM (
SELECT 1 [key], Id from table1
UNION ALL
SELECT 2, Id from table2
UNION ALL
SELECT 3, Id from table3
) x
where x.[key] = @inputValue
answered Nov 25 '18 at 14:02
hngr18hngr18
1758
That is invalid syntax for Postgres
– a_horse_with_no_name
Nov 25 '18 at 16:20
I mocked it up using SQL Server thinking it was ANSI T-SQL, I now understand that you can't declare inline variables in postgres, thank you
– hngr18
Nov 25 '18 at 16:39
1
Thank you! Correct way is: SELECT 1 as key, Id from table1
– user2078264
Nov 25 '18 at 17:03
add a comment |
0
One way to do it:
SELECT id, name
FROM table1
WHERE x = 1
UNION ALL
SELECT id, name
FROM table2
WHERE x = 2
UNION ALL
SELECT id, name
FROM table3
WHERE x = 3
Only one table's data will be returned (if x is any of those values).
answered Nov 25 '18 at 13:46
404404
3,1301727
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
});
}
});
draft saved
draft discarded
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%2f53467667%2fhow-to-use-case-or-if-statement-in-postgres-to-select-from-different-table%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
0
Inefficient solution the queries all 3 tables, but immitates a switch statement in code (assuming the retrieved columns are equivalent)
declare @inputValue int = 1
SELECT * FROM (
SELECT 1 [key], Id from table1
UNION ALL
SELECT 2, Id from table2
UNION ALL
SELECT 3, Id from table3
) x
where x.[key] = @inputValue
answered Nov 25 '18 at 14:02
hngr18hngr18
1758
That is invalid syntax for Postgres
– a_horse_with_no_name
Nov 25 '18 at 16:20
I mocked it up using SQL Server thinking it was ANSI T-SQL, I now understand that you can't declare inline variables in postgres, thank you
– hngr18
Nov 25 '18 at 16:39
1
Thank you! Correct way is: SELECT 1 as key, Id from table1
– user2078264
Nov 25 '18 at 17:03
add a comment |
0
Inefficient solution the queries all 3 tables, but immitates a switch statement in code (assuming the retrieved columns are equivalent)
declare @inputValue int = 1
SELECT * FROM (
SELECT 1 [key], Id from table1
UNION ALL
SELECT 2, Id from table2
UNION ALL
SELECT 3, Id from table3
) x
where x.[key] = @inputValue
answered Nov 25 '18 at 14:02
hngr18hngr18
1758
That is invalid syntax for Postgres
– a_horse_with_no_name
Nov 25 '18 at 16:20
I mocked it up using SQL Server thinking it was ANSI T-SQL, I now understand that you can't declare inline variables in postgres, thank you
– hngr18
Nov 25 '18 at 16:39
1
Thank you! Correct way is: SELECT 1 as key, Id from table1
– user2078264
Nov 25 '18 at 17:03
add a comment |
0
0
0
Inefficient solution the queries all 3 tables, but immitates a switch statement in code (assuming the retrieved columns are equivalent)
declare @inputValue int = 1
SELECT * FROM (
SELECT 1 [key], Id from table1
UNION ALL
SELECT 2, Id from table2
UNION ALL
SELECT 3, Id from table3
) x
where x.[key] = @inputValue
answered Nov 25 '18 at 14:02
hngr18hngr18
1758
Inefficient solution the queries all 3 tables, but immitates a switch statement in code (assuming the retrieved columns are equivalent)
declare @inputValue int = 1
SELECT * FROM (
SELECT 1 [key], Id from table1
UNION ALL
SELECT 2, Id from table2
UNION ALL
SELECT 3, Id from table3
) x
where x.[key] = @inputValue
answered Nov 25 '18 at 14:02
hngr18hngr18
1758
answered Nov 25 '18 at 14:02
hngr18hngr18
1758
answered Nov 25 '18 at 14:02
hngr18hngr18
1758
answered Nov 25 '18 at 14:02
hngr18hngr18
1758
1758
That is invalid syntax for Postgres
– a_horse_with_no_name
Nov 25 '18 at 16:20
I mocked it up using SQL Server thinking it was ANSI T-SQL, I now understand that you can't declare inline variables in postgres, thank you
– hngr18
Nov 25 '18 at 16:39
1
Thank you! Correct way is: SELECT 1 as key, Id from table1
– user2078264
Nov 25 '18 at 17:03
add a comment |
That is invalid syntax for Postgres
– a_horse_with_no_name
Nov 25 '18 at 16:20
I mocked it up using SQL Server thinking it was ANSI T-SQL, I now understand that you can't declare inline variables in postgres, thank you
– hngr18
Nov 25 '18 at 16:39
1
Thank you! Correct way is: SELECT 1 as key, Id from table1
– user2078264
Nov 25 '18 at 17:03
That is invalid syntax for Postgres
– a_horse_with_no_name
Nov 25 '18 at 16:20
That is invalid syntax for Postgres
– a_horse_with_no_name
Nov 25 '18 at 16:20
I mocked it up using SQL Server thinking it was ANSI T-SQL, I now understand that you can't declare inline variables in postgres, thank you
– hngr18
Nov 25 '18 at 16:39
I mocked it up using SQL Server thinking it was ANSI T-SQL, I now understand that you can't declare inline variables in postgres, thank you
– hngr18
Nov 25 '18 at 16:39
1
1
Thank you! Correct way is: SELECT 1 as key, Id from table1
– user2078264
Nov 25 '18 at 17:03
Thank you! Correct way is: SELECT 1 as key, Id from table1
– user2078264
Nov 25 '18 at 17:03
add a comment |
0
One way to do it:
SELECT id, name
FROM table1
WHERE x = 1
UNION ALL
SELECT id, name
FROM table2
WHERE x = 2
UNION ALL
SELECT id, name
FROM table3
WHERE x = 3
Only one table's data will be returned (if x is any of those values).
answered Nov 25 '18 at 13:46
404404
3,1301727
add a comment |
0
One way to do it:
SELECT id, name
FROM table1
WHERE x = 1
UNION ALL
SELECT id, name
FROM table2
WHERE x = 2
UNION ALL
SELECT id, name
FROM table3
WHERE x = 3
Only one table's data will be returned (if x is any of those values).
answered Nov 25 '18 at 13:46
404404
3,1301727
add a comment |
0
0
0
One way to do it:
SELECT id, name
FROM table1
WHERE x = 1
UNION ALL
SELECT id, name
FROM table2
WHERE x = 2
UNION ALL
SELECT id, name
FROM table3
WHERE x = 3
Only one table's data will be returned (if x is any of those values).
answered Nov 25 '18 at 13:46
404404
3,1301727
One way to do it:
SELECT id, name
FROM table1
WHERE x = 1
UNION ALL
SELECT id, name
FROM table2
WHERE x = 2
UNION ALL
SELECT id, name
FROM table3
WHERE x = 3
Only one table's data will be returned (if x is any of those values).
answered Nov 25 '18 at 13:46
404404
3,1301727
answered Nov 25 '18 at 13:46
404404
3,1301727
answered Nov 25 '18 at 13:46
404404
3,1301727
answered Nov 25 '18 at 13:46
404404
3,1301727
3,1301727
add a comment |
add a comment |
draft saved
draft discarded
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.
draft saved
draft discarded
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%2f53467667%2fhow-to-use-case-or-if-statement-in-postgres-to-select-from-different-table%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
Where does
xcome from?– a_horse_with_no_name
Nov 25 '18 at 13:20
It should be as parameter for TFDQuery from delphi (or it is also can be as in-parameter stored procedure in database)
– user2078264
Nov 25 '18 at 13:23
Why can't you do the
IFin Delphi? And why do you have three different tables storing the same information? From the example you have given, a single table with an additional columnxwould be better– a_horse_with_no_name
Nov 25 '18 at 13:24
It's a bad practice - dynamic creation of SQL in runtime. Different tables has different information. Only 2 fields are common - Id and Name
– user2078264
Nov 25 '18 at 13:47
Not sure I agree on IF statements being bad in code for SELECT statements, the logic has to go somewhere, SQL or Code, it's better placed where you can see it for most cases
– hngr18
Nov 25 '18 at 13:56