Query For Where Table 1 (A,B,C) for each distinct A, has rows matching all rows in Table 2 (B,C)
I'm trying to optimize a query here:
Given Table 1 (A,B,C) and Table 2 (B,C)
Given Table 1 contains many unique tuples (B,C) for each A
Select (1.A, 1.B, 1.C)
Where for a given set of rows where 1.A is distinct, the set of rows must collectively satisfy all of the rows in 2 where 1.B = 2.B and 1.C = 2.C
Example
TABLE 1
| A | B | C |
|---|----|----|
| 1 | B1 | C1 |
| 1 | B2 | C2 |
| 2 | B1 | C1 |
| 2 | B2 | C2 |
| 2 | B3 | C3 |
| 3 | B1 | C1 |
TABLE 2
| B | C |
|----|----|
| B1 | C1 |
| B2 | C2 |
RESULT
| A | B | C |
|---|----|----|
| 1 | B1 | C1 |
| 1 | B2 | C2 |
| 2 | B1 | C1 |
| 2 | B2 | C2 |
| 2 | B3 | C3 |
(Only 3 was excluded because it failed to match both rows of table 2)
Current implementation is dynamic SQL
select Result.A
from
(
SELECT vtab1.A
FROM table1 vtab1
where vtab1.[B]= 'B1' and vtab1.[C] = 'C1'
union all
SELECT b.VaultObjectId
FROM table1 vtab2
where vtab2.[B]= 'B2' and vtab2.[C] = 'C2'
) AS Result
Group By A
HAVING
COUNT(A) = 2
However this is all generated dynamically, and doesn't remerge my A with the matching B (which I suppose is easy enough)
Related: Is there any way to "name" a query to be used later (not computed, but deferred?)
sql-server t-sql query-performance optimization relational-division
edited May 5 '18 at 15:32
ypercubeᵀᴹ
75.6k11128211
asked May 4 '18 at 19:37
Daniel GreenDaniel Green
61
bumped to the homepage by Community♦ 4 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
I'm trying to optimize a query here:
Given Table 1 (A,B,C) and Table 2 (B,C)
Given Table 1 contains many unique tuples (B,C) for each A
Select (1.A, 1.B, 1.C)
Where for a given set of rows where 1.A is distinct, the set of rows must collectively satisfy all of the rows in 2 where 1.B = 2.B and 1.C = 2.C
Example
TABLE 1
| A | B | C |
|---|----|----|
| 1 | B1 | C1 |
| 1 | B2 | C2 |
| 2 | B1 | C1 |
| 2 | B2 | C2 |
| 2 | B3 | C3 |
| 3 | B1 | C1 |
TABLE 2
| B | C |
|----|----|
| B1 | C1 |
| B2 | C2 |
RESULT
| A | B | C |
|---|----|----|
| 1 | B1 | C1 |
| 1 | B2 | C2 |
| 2 | B1 | C1 |
| 2 | B2 | C2 |
| 2 | B3 | C3 |
(Only 3 was excluded because it failed to match both rows of table 2)
Current implementation is dynamic SQL
select Result.A
from
(
SELECT vtab1.A
FROM table1 vtab1
where vtab1.[B]= 'B1' and vtab1.[C] = 'C1'
union all
SELECT b.VaultObjectId
FROM table1 vtab2
where vtab2.[B]= 'B2' and vtab2.[C] = 'C2'
) AS Result
Group By A
HAVING
COUNT(A) = 2
However this is all generated dynamically, and doesn't remerge my A with the matching B (which I suppose is easy enough)
Related: Is there any way to "name" a query to be used later (not computed, but deferred?)
sql-server t-sql query-performance optimization relational-division
edited May 5 '18 at 15:32
ypercubeᵀᴹ
75.6k11128211
asked May 4 '18 at 19:37
Daniel GreenDaniel Green
61
bumped to the homepage by Community♦ 4 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
2
The problem is called "relational division".
– ypercubeᵀᴹ
May 4 '18 at 20:08
add a comment |
I'm trying to optimize a query here:
Given Table 1 (A,B,C) and Table 2 (B,C)
Given Table 1 contains many unique tuples (B,C) for each A
Select (1.A, 1.B, 1.C)
Where for a given set of rows where 1.A is distinct, the set of rows must collectively satisfy all of the rows in 2 where 1.B = 2.B and 1.C = 2.C
Example
TABLE 1
| A | B | C |
|---|----|----|
| 1 | B1 | C1 |
| 1 | B2 | C2 |
| 2 | B1 | C1 |
| 2 | B2 | C2 |
| 2 | B3 | C3 |
| 3 | B1 | C1 |
TABLE 2
| B | C |
|----|----|
| B1 | C1 |
| B2 | C2 |
RESULT
| A | B | C |
|---|----|----|
| 1 | B1 | C1 |
| 1 | B2 | C2 |
| 2 | B1 | C1 |
| 2 | B2 | C2 |
| 2 | B3 | C3 |
(Only 3 was excluded because it failed to match both rows of table 2)
Current implementation is dynamic SQL
select Result.A
from
(
SELECT vtab1.A
FROM table1 vtab1
where vtab1.[B]= 'B1' and vtab1.[C] = 'C1'
union all
SELECT b.VaultObjectId
FROM table1 vtab2
where vtab2.[B]= 'B2' and vtab2.[C] = 'C2'
) AS Result
Group By A
HAVING
COUNT(A) = 2
However this is all generated dynamically, and doesn't remerge my A with the matching B (which I suppose is easy enough)
Related: Is there any way to "name" a query to be used later (not computed, but deferred?)
sql-server t-sql query-performance optimization relational-division
edited May 5 '18 at 15:32
ypercubeᵀᴹ
75.6k11128211
asked May 4 '18 at 19:37
Daniel GreenDaniel Green
61
I'm trying to optimize a query here:
Given Table 1 (A,B,C) and Table 2 (B,C)
Given Table 1 contains many unique tuples (B,C) for each A
Select (1.A, 1.B, 1.C)
Where for a given set of rows where 1.A is distinct, the set of rows must collectively satisfy all of the rows in 2 where 1.B = 2.B and 1.C = 2.C
Example
TABLE 1
| A | B | C |
|---|----|----|
| 1 | B1 | C1 |
| 1 | B2 | C2 |
| 2 | B1 | C1 |
| 2 | B2 | C2 |
| 2 | B3 | C3 |
| 3 | B1 | C1 |
TABLE 2
| B | C |
|----|----|
| B1 | C1 |
| B2 | C2 |
RESULT
| A | B | C |
|---|----|----|
| 1 | B1 | C1 |
| 1 | B2 | C2 |
| 2 | B1 | C1 |
| 2 | B2 | C2 |
| 2 | B3 | C3 |
(Only 3 was excluded because it failed to match both rows of table 2)
Current implementation is dynamic SQL
select Result.A
from
(
SELECT vtab1.A
FROM table1 vtab1
where vtab1.[B]= 'B1' and vtab1.[C] = 'C1'
union all
SELECT b.VaultObjectId
FROM table1 vtab2
where vtab2.[B]= 'B2' and vtab2.[C] = 'C2'
) AS Result
Group By A
HAVING
COUNT(A) = 2
However this is all generated dynamically, and doesn't remerge my A with the matching B (which I suppose is easy enough)
Related: Is there any way to "name" a query to be used later (not computed, but deferred?)
sql-server t-sql query-performance optimization relational-division
sql-server t-sql query-performance optimization relational-division
edited May 5 '18 at 15:32
ypercubeᵀᴹ
75.6k11128211
asked May 4 '18 at 19:37
Daniel GreenDaniel Green
61
edited May 5 '18 at 15:32
ypercubeᵀᴹ
75.6k11128211
asked May 4 '18 at 19:37
Daniel GreenDaniel Green
61
edited May 5 '18 at 15:32
ypercubeᵀᴹ
75.6k11128211
edited May 5 '18 at 15:32
ypercubeᵀᴹ
75.6k11128211
edited May 5 '18 at 15:32
ypercubeᵀᴹ
75.6k11128211
75.6k11128211
asked May 4 '18 at 19:37
Daniel GreenDaniel Green
61
asked May 4 '18 at 19:37
Daniel GreenDaniel Green
61
asked May 4 '18 at 19:37
Daniel GreenDaniel Green
61
61
bumped to the homepage by Community♦ 4 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
bumped to the homepage by Community♦ 4 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
2
The problem is called "relational division".
– ypercubeᵀᴹ
May 4 '18 at 20:08
add a comment |
2
The problem is called "relational division".
– ypercubeᵀᴹ
May 4 '18 at 20:08
2
2
The problem is called "relational division".
– ypercubeᵀᴹ
May 4 '18 at 20:08
The problem is called "relational division".
– ypercubeᵀᴹ
May 4 '18 at 20:08
add a comment |
2 Answers
2
active
oldest
votes
select *
from t1
where t1.a in ( select t1.a
from t1
join t2
on t2.b = t1.b
and t2.c = t2.c
group by t1.a
having count(*) = (select count(*) from t2)
)
answered May 5 '18 at 12:30
paparazzopaparazzo
4,6141230
add a comment |
I find that the easiest way to deal with relational division challenges is to phrase the question in the negative form:
"Show me all rows from T1, for which there doesn't exist a row in T2, for which there doesn't exist another row from T1, with the same A value, and matching B and C values". It seems weird at first, but once you get used to it, the translation to SQL is immediate, elegant, and typically the most efficient:
SELECT *
FROM T1
WHERE NOT EXISTS (
SELECT NULL
FROM T2
WHERE NOT EXISTS (
SELECT NULL
FROM T1 AS T1A
WHERE T1A.B = T2.B
AND
T1A.C = T2.C
AND
T1A.A = T1.A
)
);
answered May 5 '18 at 20:48
SQLRaptorSQLRaptor
2,2711319
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
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: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
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%2fdba.stackexchange.com%2fquestions%2f205881%2fquery-for-where-table-1-a-b-c-for-each-distinct-a-has-rows-matching-all-rows%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
select *
from t1
where t1.a in ( select t1.a
from t1
join t2
on t2.b = t1.b
and t2.c = t2.c
group by t1.a
having count(*) = (select count(*) from t2)
)
answered May 5 '18 at 12:30
paparazzopaparazzo
4,6141230
add a comment |
select *
from t1
where t1.a in ( select t1.a
from t1
join t2
on t2.b = t1.b
and t2.c = t2.c
group by t1.a
having count(*) = (select count(*) from t2)
)
answered May 5 '18 at 12:30
paparazzopaparazzo
4,6141230
add a comment |
select *
from t1
where t1.a in ( select t1.a
from t1
join t2
on t2.b = t1.b
and t2.c = t2.c
group by t1.a
having count(*) = (select count(*) from t2)
)
answered May 5 '18 at 12:30
paparazzopaparazzo
4,6141230
select *
from t1
where t1.a in ( select t1.a
from t1
join t2
on t2.b = t1.b
and t2.c = t2.c
group by t1.a
having count(*) = (select count(*) from t2)
)
answered May 5 '18 at 12:30
paparazzopaparazzo
4,6141230
answered May 5 '18 at 12:30
paparazzopaparazzo
4,6141230
answered May 5 '18 at 12:30
paparazzopaparazzo
4,6141230
answered May 5 '18 at 12:30
paparazzopaparazzo
4,6141230
4,6141230
add a comment |
add a comment |
I find that the easiest way to deal with relational division challenges is to phrase the question in the negative form:
"Show me all rows from T1, for which there doesn't exist a row in T2, for which there doesn't exist another row from T1, with the same A value, and matching B and C values". It seems weird at first, but once you get used to it, the translation to SQL is immediate, elegant, and typically the most efficient:
SELECT *
FROM T1
WHERE NOT EXISTS (
SELECT NULL
FROM T2
WHERE NOT EXISTS (
SELECT NULL
FROM T1 AS T1A
WHERE T1A.B = T2.B
AND
T1A.C = T2.C
AND
T1A.A = T1.A
)
);
answered May 5 '18 at 20:48
SQLRaptorSQLRaptor
2,2711319
add a comment |
I find that the easiest way to deal with relational division challenges is to phrase the question in the negative form:
"Show me all rows from T1, for which there doesn't exist a row in T2, for which there doesn't exist another row from T1, with the same A value, and matching B and C values". It seems weird at first, but once you get used to it, the translation to SQL is immediate, elegant, and typically the most efficient:
SELECT *
FROM T1
WHERE NOT EXISTS (
SELECT NULL
FROM T2
WHERE NOT EXISTS (
SELECT NULL
FROM T1 AS T1A
WHERE T1A.B = T2.B
AND
T1A.C = T2.C
AND
T1A.A = T1.A
)
);
answered May 5 '18 at 20:48
SQLRaptorSQLRaptor
2,2711319
add a comment |
I find that the easiest way to deal with relational division challenges is to phrase the question in the negative form:
"Show me all rows from T1, for which there doesn't exist a row in T2, for which there doesn't exist another row from T1, with the same A value, and matching B and C values". It seems weird at first, but once you get used to it, the translation to SQL is immediate, elegant, and typically the most efficient:
SELECT *
FROM T1
WHERE NOT EXISTS (
SELECT NULL
FROM T2
WHERE NOT EXISTS (
SELECT NULL
FROM T1 AS T1A
WHERE T1A.B = T2.B
AND
T1A.C = T2.C
AND
T1A.A = T1.A
)
);
answered May 5 '18 at 20:48
SQLRaptorSQLRaptor
2,2711319
I find that the easiest way to deal with relational division challenges is to phrase the question in the negative form:
"Show me all rows from T1, for which there doesn't exist a row in T2, for which there doesn't exist another row from T1, with the same A value, and matching B and C values". It seems weird at first, but once you get used to it, the translation to SQL is immediate, elegant, and typically the most efficient:
SELECT *
FROM T1
WHERE NOT EXISTS (
SELECT NULL
FROM T2
WHERE NOT EXISTS (
SELECT NULL
FROM T1 AS T1A
WHERE T1A.B = T2.B
AND
T1A.C = T2.C
AND
T1A.A = T1.A
)
);
answered May 5 '18 at 20:48
SQLRaptorSQLRaptor
2,2711319
answered May 5 '18 at 20:48
SQLRaptorSQLRaptor
2,2711319
answered May 5 '18 at 20:48
SQLRaptorSQLRaptor
2,2711319
answered May 5 '18 at 20:48
SQLRaptorSQLRaptor
2,2711319
2,2711319
add a comment |
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- 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%2fdba.stackexchange.com%2fquestions%2f205881%2fquery-for-where-table-1-a-b-c-for-each-distinct-a-has-rows-matching-all-rows%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
2
The problem is called "relational division".
– ypercubeᵀᴹ
May 4 '18 at 20:08