Oracle SQL: How to join tables containing ALL / Wildcard values?
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
I hope you can help me with the following :-)
Let's make it simple:
I have table_A containing
COUNTRY, ACCOUNT, PRODUCT AND ORIGIN
and table_B containing
COUNTRY, ACCOUNT, PRODUCT, ORIGIN and TARIFF
My issue is that each column of table_B, except COUNTRY, can contain a "ALL" value, which means that the tariff applies to all accounts or product or origin.
See image...
How to use conditional join to use or skip join based on the 'ALL' values?
To get the tariff for Table_A data??
Thanks a lot
sql oracle join conditional
edited Nov 16 '18 at 12:06
Gordon Linoff
796k37318423
asked Nov 16 '18 at 10:38
bverslegbversleg
11
add a comment |
I hope you can help me with the following :-)
Let's make it simple:
I have table_A containing
COUNTRY, ACCOUNT, PRODUCT AND ORIGIN
and table_B containing
COUNTRY, ACCOUNT, PRODUCT, ORIGIN and TARIFF
My issue is that each column of table_B, except COUNTRY, can contain a "ALL" value, which means that the tariff applies to all accounts or product or origin.
See image...
How to use conditional join to use or skip join based on the 'ALL' values?
To get the tariff for Table_A data??
Thanks a lot
sql oracle join conditional
edited Nov 16 '18 at 12:06
Gordon Linoff
796k37318423
asked Nov 16 '18 at 10:38
bverslegbversleg
11
1
Please don't link images. Edit your request and copy and paste the table as text please.
– Thorsten Kettner
Nov 16 '18 at 10:41
So for an A record you want the tariff from B. What if there are multiple tariffs matching? Do you want to select all matching tarrffs or only one? If only one: which?
– Thorsten Kettner
Nov 16 '18 at 10:44
Please provide sample data forAand expected results. What happens when multiple rows match?
– Gordon Linoff
Nov 16 '18 at 12:07
add a comment |
I hope you can help me with the following :-)
Let's make it simple:
I have table_A containing
COUNTRY, ACCOUNT, PRODUCT AND ORIGIN
and table_B containing
COUNTRY, ACCOUNT, PRODUCT, ORIGIN and TARIFF
My issue is that each column of table_B, except COUNTRY, can contain a "ALL" value, which means that the tariff applies to all accounts or product or origin.
See image...
How to use conditional join to use or skip join based on the 'ALL' values?
To get the tariff for Table_A data??
Thanks a lot
sql oracle join conditional
edited Nov 16 '18 at 12:06
Gordon Linoff
796k37318423
asked Nov 16 '18 at 10:38
bverslegbversleg
11
I hope you can help me with the following :-)
Let's make it simple:
I have table_A containing
COUNTRY, ACCOUNT, PRODUCT AND ORIGIN
and table_B containing
COUNTRY, ACCOUNT, PRODUCT, ORIGIN and TARIFF
My issue is that each column of table_B, except COUNTRY, can contain a "ALL" value, which means that the tariff applies to all accounts or product or origin.
See image...
How to use conditional join to use or skip join based on the 'ALL' values?
To get the tariff for Table_A data??
Thanks a lot
sql oracle join conditional
sql oracle join conditional
edited Nov 16 '18 at 12:06
Gordon Linoff
796k37318423
asked Nov 16 '18 at 10:38
bverslegbversleg
11
edited Nov 16 '18 at 12:06
Gordon Linoff
796k37318423
asked Nov 16 '18 at 10:38
bverslegbversleg
11
edited Nov 16 '18 at 12:06
Gordon Linoff
796k37318423
edited Nov 16 '18 at 12:06
Gordon Linoff
796k37318423
edited Nov 16 '18 at 12:06
Gordon Linoff
796k37318423
796k37318423
asked Nov 16 '18 at 10:38
bverslegbversleg
11
asked Nov 16 '18 at 10:38
bverslegbversleg
11
asked Nov 16 '18 at 10:38
bverslegbversleg
11
11
1
Please don't link images. Edit your request and copy and paste the table as text please.
– Thorsten Kettner
Nov 16 '18 at 10:41
So for an A record you want the tariff from B. What if there are multiple tariffs matching? Do you want to select all matching tarrffs or only one? If only one: which?
– Thorsten Kettner
Nov 16 '18 at 10:44
Please provide sample data forAand expected results. What happens when multiple rows match?
– Gordon Linoff
Nov 16 '18 at 12:07
add a comment |
1
Please don't link images. Edit your request and copy and paste the table as text please.
– Thorsten Kettner
Nov 16 '18 at 10:41
So for an A record you want the tariff from B. What if there are multiple tariffs matching? Do you want to select all matching tarrffs or only one? If only one: which?
– Thorsten Kettner
Nov 16 '18 at 10:44
Please provide sample data forAand expected results. What happens when multiple rows match?
– Gordon Linoff
Nov 16 '18 at 12:07
1
1
Please don't link images. Edit your request and copy and paste the table as text please.
– Thorsten Kettner
Nov 16 '18 at 10:41
Please don't link images. Edit your request and copy and paste the table as text please.
– Thorsten Kettner
Nov 16 '18 at 10:41
So for an A record you want the tariff from B. What if there are multiple tariffs matching? Do you want to select all matching tarrffs or only one? If only one: which?
– Thorsten Kettner
Nov 16 '18 at 10:44
So for an A record you want the tariff from B. What if there are multiple tariffs matching? Do you want to select all matching tarrffs or only one? If only one: which?
– Thorsten Kettner
Nov 16 '18 at 10:44
Please provide sample data for
A and expected results. What happens when multiple rows match?– Gordon Linoff
Nov 16 '18 at 12:07
Please provide sample data for
A and expected results. What happens when multiple rows match?– Gordon Linoff
Nov 16 '18 at 12:07
add a comment |
2 Answers
2
active
oldest
votes
Use AND, OR, and parentheses in your WHERE clause.
select *
from table_a a
join table_b b on a.country = b.country
and (a.account = b.account or b.account = 'ALL')
and (a.product = b.product or b.product = 'ALL')
and (a.origin = b.origin or b.origin = 'ALL');
answered Nov 16 '18 at 10:49
Thorsten KettnerThorsten Kettner
53k32643
add a comment |
Maybe this could help :
SELECT A.*, B.TARIFF
FROM table_A A, table_B B
WHERE ( B.COUNTRY = 'ALL' OR (B.COUNTRY = A.COUNTRY) )
AND ( B.ACCOUNT = 'ALL' OR (B.ACCOUNT = A.ACCOUNT) )
AND ( B.PRODUCT = 'ALL' OR (B.PRODUCT = A.PRODUCT) )
AND ( B.ORIGIN = 'ALL' OR (B.ORIGIN = A.ORIGIN ) )
Then it all depends of how are your tables defined (primary keys, unique keys..). Meaning, depending on tables stucture and data, you may have some duplicate tarifs for a single record from table_A.
answered Nov 16 '18 at 10:54
imAProgrammerimAProgrammer
13
You shouldn't use comma-separated joins. They have been made redundant in the SQL standard in 1992 for a reason. Use proper joins instead (FROM table_A A JOIN table_B B ON ...).
– Thorsten Kettner
Nov 16 '18 at 11:02
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%2f53336144%2foracle-sql-how-to-join-tables-containing-all-wildcard-values%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
Use AND, OR, and parentheses in your WHERE clause.
select *
from table_a a
join table_b b on a.country = b.country
and (a.account = b.account or b.account = 'ALL')
and (a.product = b.product or b.product = 'ALL')
and (a.origin = b.origin or b.origin = 'ALL');
answered Nov 16 '18 at 10:49
Thorsten KettnerThorsten Kettner
53k32643
add a comment |
Use AND, OR, and parentheses in your WHERE clause.
select *
from table_a a
join table_b b on a.country = b.country
and (a.account = b.account or b.account = 'ALL')
and (a.product = b.product or b.product = 'ALL')
and (a.origin = b.origin or b.origin = 'ALL');
answered Nov 16 '18 at 10:49
Thorsten KettnerThorsten Kettner
53k32643
add a comment |
Use AND, OR, and parentheses in your WHERE clause.
select *
from table_a a
join table_b b on a.country = b.country
and (a.account = b.account or b.account = 'ALL')
and (a.product = b.product or b.product = 'ALL')
and (a.origin = b.origin or b.origin = 'ALL');
answered Nov 16 '18 at 10:49
Thorsten KettnerThorsten Kettner
53k32643
Use AND, OR, and parentheses in your WHERE clause.
select *
from table_a a
join table_b b on a.country = b.country
and (a.account = b.account or b.account = 'ALL')
and (a.product = b.product or b.product = 'ALL')
and (a.origin = b.origin or b.origin = 'ALL');
answered Nov 16 '18 at 10:49
Thorsten KettnerThorsten Kettner
53k32643
answered Nov 16 '18 at 10:49
Thorsten KettnerThorsten Kettner
53k32643
answered Nov 16 '18 at 10:49
Thorsten KettnerThorsten Kettner
53k32643
answered Nov 16 '18 at 10:49
Thorsten KettnerThorsten Kettner
53k32643
53k32643
add a comment |
add a comment |
Maybe this could help :
SELECT A.*, B.TARIFF
FROM table_A A, table_B B
WHERE ( B.COUNTRY = 'ALL' OR (B.COUNTRY = A.COUNTRY) )
AND ( B.ACCOUNT = 'ALL' OR (B.ACCOUNT = A.ACCOUNT) )
AND ( B.PRODUCT = 'ALL' OR (B.PRODUCT = A.PRODUCT) )
AND ( B.ORIGIN = 'ALL' OR (B.ORIGIN = A.ORIGIN ) )
Then it all depends of how are your tables defined (primary keys, unique keys..). Meaning, depending on tables stucture and data, you may have some duplicate tarifs for a single record from table_A.
answered Nov 16 '18 at 10:54
imAProgrammerimAProgrammer
13
You shouldn't use comma-separated joins. They have been made redundant in the SQL standard in 1992 for a reason. Use proper joins instead (FROM table_A A JOIN table_B B ON ...).
– Thorsten Kettner
Nov 16 '18 at 11:02
add a comment |
Maybe this could help :
SELECT A.*, B.TARIFF
FROM table_A A, table_B B
WHERE ( B.COUNTRY = 'ALL' OR (B.COUNTRY = A.COUNTRY) )
AND ( B.ACCOUNT = 'ALL' OR (B.ACCOUNT = A.ACCOUNT) )
AND ( B.PRODUCT = 'ALL' OR (B.PRODUCT = A.PRODUCT) )
AND ( B.ORIGIN = 'ALL' OR (B.ORIGIN = A.ORIGIN ) )
Then it all depends of how are your tables defined (primary keys, unique keys..). Meaning, depending on tables stucture and data, you may have some duplicate tarifs for a single record from table_A.
answered Nov 16 '18 at 10:54
imAProgrammerimAProgrammer
13
You shouldn't use comma-separated joins. They have been made redundant in the SQL standard in 1992 for a reason. Use proper joins instead (FROM table_A A JOIN table_B B ON ...).
– Thorsten Kettner
Nov 16 '18 at 11:02
add a comment |
Maybe this could help :
SELECT A.*, B.TARIFF
FROM table_A A, table_B B
WHERE ( B.COUNTRY = 'ALL' OR (B.COUNTRY = A.COUNTRY) )
AND ( B.ACCOUNT = 'ALL' OR (B.ACCOUNT = A.ACCOUNT) )
AND ( B.PRODUCT = 'ALL' OR (B.PRODUCT = A.PRODUCT) )
AND ( B.ORIGIN = 'ALL' OR (B.ORIGIN = A.ORIGIN ) )
Then it all depends of how are your tables defined (primary keys, unique keys..). Meaning, depending on tables stucture and data, you may have some duplicate tarifs for a single record from table_A.
answered Nov 16 '18 at 10:54
imAProgrammerimAProgrammer
13
Maybe this could help :
SELECT A.*, B.TARIFF
FROM table_A A, table_B B
WHERE ( B.COUNTRY = 'ALL' OR (B.COUNTRY = A.COUNTRY) )
AND ( B.ACCOUNT = 'ALL' OR (B.ACCOUNT = A.ACCOUNT) )
AND ( B.PRODUCT = 'ALL' OR (B.PRODUCT = A.PRODUCT) )
AND ( B.ORIGIN = 'ALL' OR (B.ORIGIN = A.ORIGIN ) )
Then it all depends of how are your tables defined (primary keys, unique keys..). Meaning, depending on tables stucture and data, you may have some duplicate tarifs for a single record from table_A.
answered Nov 16 '18 at 10:54
imAProgrammerimAProgrammer
13
answered Nov 16 '18 at 10:54
imAProgrammerimAProgrammer
13
answered Nov 16 '18 at 10:54
imAProgrammerimAProgrammer
13
answered Nov 16 '18 at 10:54
imAProgrammerimAProgrammer
13
13
You shouldn't use comma-separated joins. They have been made redundant in the SQL standard in 1992 for a reason. Use proper joins instead (FROM table_A A JOIN table_B B ON ...).
– Thorsten Kettner
Nov 16 '18 at 11:02
add a comment |
You shouldn't use comma-separated joins. They have been made redundant in the SQL standard in 1992 for a reason. Use proper joins instead (FROM table_A A JOIN table_B B ON ...).
– Thorsten Kettner
Nov 16 '18 at 11:02
You shouldn't use comma-separated joins. They have been made redundant in the SQL standard in 1992 for a reason. Use proper joins instead (
FROM table_A A JOIN table_B B ON ...).– Thorsten Kettner
Nov 16 '18 at 11:02
You shouldn't use comma-separated joins. They have been made redundant in the SQL standard in 1992 for a reason. Use proper joins instead (
FROM table_A A JOIN table_B B ON ...).– Thorsten Kettner
Nov 16 '18 at 11:02
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%2f53336144%2foracle-sql-how-to-join-tables-containing-all-wildcard-values%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
1
Please don't link images. Edit your request and copy and paste the table as text please.
– Thorsten Kettner
Nov 16 '18 at 10:41
So for an A record you want the tariff from B. What if there are multiple tariffs matching? Do you want to select all matching tarrffs or only one? If only one: which?
– Thorsten Kettner
Nov 16 '18 at 10:44
Please provide sample data for
Aand expected results. What happens when multiple rows match?– Gordon Linoff
Nov 16 '18 at 12:07