SQL Match group of records to another group of recordsHow can I prevent SQL injection in PHP?Add a column with a default value to an existing table in SQL ServerSQL update from one Table to another based on a ID matchSQLite - UPSERT *not* INSERT or REPLACEInserting multiple rows in a single SQL query?Retrieving the last record in each group - MySQLHow do I UPDATE from a SELECT in SQL Server?Finding duplicate values in a SQL tableSelect first row in each GROUP BY group?How to import an SQL file using the command line in MySQL?
Why is delta-v is the most useful quantity for planning space travel?
For airliners, what prevents wing strikes on landing in bad weather?
Freedom of speech and where it applies
In Star Trek IV, why did the Bounty go back to a time when whales were already rare?
Can a Gentile theist be saved?
Is there an Impartial Brexit Deal comparison site?
Greatest common substring
Resetting two CD4017 counters simultaneously, only one resets
How to prevent YouTube from showing already watched videos?
Is there enough fresh water in the world to eradicate the drinking water crisis?
Why does this part of the Space Shuttle launch pad seem to be floating in air?
What will be the temperature on Earth when Sun finishes its main sequence?
Simulating a probability of 1 of 2^N with less than N random bits
How to deal with or prevent idle in the test team?
The most efficient algorithm to find all possible integer pairs which sum to a given integer
Lifted its hind leg on or lifted its hind leg towards?
Adding empty element to declared container without declaring type of element
How can I raise concerns with a new DM about XP splitting?
Why are on-board computers allowed to change controls without notifying the pilots?
Is it okay / does it make sense for another player to join a running game of Munchkin?
Why isn't KTEX's runway designation 10/28 instead of 9/27?
Why Were Madagascar and New Zealand Discovered So Late?
Science Fiction story where a man invents a machine that can help him watch history unfold
Is there a good way to store credentials outside of a password manager?
SQL Match group of records to another group of records
How can I prevent SQL injection in PHP?Add a column with a default value to an existing table in SQL ServerSQL update from one Table to another based on a ID matchSQLite - UPSERT *not* INSERT or REPLACEInserting multiple rows in a single SQL query?Retrieving the last record in each group - MySQLHow do I UPDATE from a SELECT in SQL Server?Finding duplicate values in a SQL tableSelect first row in each GROUP BY group?How to import an SQL file using the command line in MySQL?
Is there SQL statement to match up multiple records to an exact match of multiple records in another table?
Lets say I have table A
ID | List# | Item
1 | 5 | A
2 | 5 | C
3 | 5 | B
4 | 6 | A
5 | 6 | D
*I purposely made Items 'ABC' out of order as the order of the records I receive may be out of order.
Table B
ID | Group | Item
1 | AAA | A
2 | AAA | B
3 | AAA | C
4 | AAA | D
5 | BBB | A
6 | BBB | B
7 | BBB | C
8 | DDD | A
If looking at the first table, I would want List# 5 to return a match only for group 'BBB', as all (and only) three records match.
sql
edited Mar 21 at 14:55
apomene
11k93559
asked Mar 21 at 14:53
Shawn ReeverShawn Reever
1
New contributor
Shawn Reever is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
Is there SQL statement to match up multiple records to an exact match of multiple records in another table?
Lets say I have table A
ID | List# | Item
1 | 5 | A
2 | 5 | C
3 | 5 | B
4 | 6 | A
5 | 6 | D
*I purposely made Items 'ABC' out of order as the order of the records I receive may be out of order.
Table B
ID | Group | Item
1 | AAA | A
2 | AAA | B
3 | AAA | C
4 | AAA | D
5 | BBB | A
6 | BBB | B
7 | BBB | C
8 | DDD | A
If looking at the first table, I would want List# 5 to return a match only for group 'BBB', as all (and only) three records match.
sql
edited Mar 21 at 14:55
apomene
11k93559
asked Mar 21 at 14:53
Shawn ReeverShawn Reever
1
New contributor
Shawn Reever is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Please tag with the database you are using.
– Gordon Linoff
Mar 21 at 14:55
SQL does not natively implement table division. It can be simulated, however.
– The Impaler
Mar 21 at 14:58
add a comment |
Is there SQL statement to match up multiple records to an exact match of multiple records in another table?
Lets say I have table A
ID | List# | Item
1 | 5 | A
2 | 5 | C
3 | 5 | B
4 | 6 | A
5 | 6 | D
*I purposely made Items 'ABC' out of order as the order of the records I receive may be out of order.
Table B
ID | Group | Item
1 | AAA | A
2 | AAA | B
3 | AAA | C
4 | AAA | D
5 | BBB | A
6 | BBB | B
7 | BBB | C
8 | DDD | A
If looking at the first table, I would want List# 5 to return a match only for group 'BBB', as all (and only) three records match.
sql
edited Mar 21 at 14:55
apomene
11k93559
asked Mar 21 at 14:53
Shawn ReeverShawn Reever
1
New contributor
Shawn Reever is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Is there SQL statement to match up multiple records to an exact match of multiple records in another table?
Lets say I have table A
ID | List# | Item
1 | 5 | A
2 | 5 | C
3 | 5 | B
4 | 6 | A
5 | 6 | D
*I purposely made Items 'ABC' out of order as the order of the records I receive may be out of order.
Table B
ID | Group | Item
1 | AAA | A
2 | AAA | B
3 | AAA | C
4 | AAA | D
5 | BBB | A
6 | BBB | B
7 | BBB | C
8 | DDD | A
If looking at the first table, I would want List# 5 to return a match only for group 'BBB', as all (and only) three records match.
sql
sql
edited Mar 21 at 14:55
apomene
11k93559
asked Mar 21 at 14:53
Shawn ReeverShawn Reever
1
New contributor
Shawn Reever is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
edited Mar 21 at 14:55
apomene
11k93559
asked Mar 21 at 14:53
Shawn ReeverShawn Reever
1
New contributor
Shawn Reever is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
edited Mar 21 at 14:55
apomene
11k93559
edited Mar 21 at 14:55
apomene
11k93559
edited Mar 21 at 14:55
apomene
11k93559
11k93559
asked Mar 21 at 14:53
Shawn ReeverShawn Reever
1
New contributor
Shawn Reever is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked Mar 21 at 14:53
Shawn ReeverShawn Reever
1
asked Mar 21 at 14:53
Shawn ReeverShawn Reever
1
1
New contributor
Shawn Reever is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
New contributor
Shawn Reever is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Shawn Reever is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Please tag with the database you are using.
– Gordon Linoff
Mar 21 at 14:55
SQL does not natively implement table division. It can be simulated, however.
– The Impaler
Mar 21 at 14:58
add a comment |
Please tag with the database you are using.
– Gordon Linoff
Mar 21 at 14:55
SQL does not natively implement table division. It can be simulated, however.
– The Impaler
Mar 21 at 14:58
Please tag with the database you are using.
– Gordon Linoff
Mar 21 at 14:55
Please tag with the database you are using.
– Gordon Linoff
Mar 21 at 14:55
SQL does not natively implement table division. It can be simulated, however.
– The Impaler
Mar 21 at 14:58
SQL does not natively implement table division. It can be simulated, however.
– The Impaler
Mar 21 at 14:58
add a comment |
2 Answers
2
active
oldest
votes
The simplest way is to aggregate into a string or array and join. Standard SQL supports listagg(), so you can do:
select a.list, b.list, a.items
from (select a.list, listagg(item, ',') within group (order by item) as items
from a
group by a.list
) a join
(select b.list, listagg(item, ',') within group (order by item) as items
from b
group by b.list
) b
on a.items = b.items;
Not all databases support listagg(). Many -- but not all -- have similar functionality. This is simpler than the "standard" SQL approach.
answered Mar 21 at 14:57
Gordon LinoffGordon Linoff
790k35314418
add a comment |
You can simulate database division. It's a little bit cumbersome but here it is:
with
x as (
select
from a
where a.list = 5
),
y as (
select grp, count(*) as cnt
from b
join x on x.item = b.item
group by grp
)
select grp
from y
where cnt = (select count(*) from x)
answered Mar 21 at 15:03
The ImpalerThe Impaler
11.4k41441
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
);
);
Shawn Reever is a new contributor. Be nice, and check out our Code of Conduct.
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%2f55283274%2fsql-match-group-of-records-to-another-group-of-records%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
The simplest way is to aggregate into a string or array and join. Standard SQL supports listagg(), so you can do:
select a.list, b.list, a.items
from (select a.list, listagg(item, ',') within group (order by item) as items
from a
group by a.list
) a join
(select b.list, listagg(item, ',') within group (order by item) as items
from b
group by b.list
) b
on a.items = b.items;
Not all databases support listagg(). Many -- but not all -- have similar functionality. This is simpler than the "standard" SQL approach.
answered Mar 21 at 14:57
Gordon LinoffGordon Linoff
790k35314418
add a comment |
The simplest way is to aggregate into a string or array and join. Standard SQL supports listagg(), so you can do:
select a.list, b.list, a.items
from (select a.list, listagg(item, ',') within group (order by item) as items
from a
group by a.list
) a join
(select b.list, listagg(item, ',') within group (order by item) as items
from b
group by b.list
) b
on a.items = b.items;
Not all databases support listagg(). Many -- but not all -- have similar functionality. This is simpler than the "standard" SQL approach.
answered Mar 21 at 14:57
Gordon LinoffGordon Linoff
790k35314418
add a comment |
The simplest way is to aggregate into a string or array and join. Standard SQL supports listagg(), so you can do:
select a.list, b.list, a.items
from (select a.list, listagg(item, ',') within group (order by item) as items
from a
group by a.list
) a join
(select b.list, listagg(item, ',') within group (order by item) as items
from b
group by b.list
) b
on a.items = b.items;
Not all databases support listagg(). Many -- but not all -- have similar functionality. This is simpler than the "standard" SQL approach.
answered Mar 21 at 14:57
Gordon LinoffGordon Linoff
790k35314418
The simplest way is to aggregate into a string or array and join. Standard SQL supports listagg(), so you can do:
select a.list, b.list, a.items
from (select a.list, listagg(item, ',') within group (order by item) as items
from a
group by a.list
) a join
(select b.list, listagg(item, ',') within group (order by item) as items
from b
group by b.list
) b
on a.items = b.items;
Not all databases support listagg(). Many -- but not all -- have similar functionality. This is simpler than the "standard" SQL approach.
answered Mar 21 at 14:57
Gordon LinoffGordon Linoff
790k35314418
answered Mar 21 at 14:57
Gordon LinoffGordon Linoff
790k35314418
answered Mar 21 at 14:57
Gordon LinoffGordon Linoff
790k35314418
answered Mar 21 at 14:57
Gordon LinoffGordon Linoff
790k35314418
790k35314418
add a comment |
add a comment |
You can simulate database division. It's a little bit cumbersome but here it is:
with
x as (
select
from a
where a.list = 5
),
y as (
select grp, count(*) as cnt
from b
join x on x.item = b.item
group by grp
)
select grp
from y
where cnt = (select count(*) from x)
answered Mar 21 at 15:03
The ImpalerThe Impaler
11.4k41441
add a comment |
You can simulate database division. It's a little bit cumbersome but here it is:
with
x as (
select
from a
where a.list = 5
),
y as (
select grp, count(*) as cnt
from b
join x on x.item = b.item
group by grp
)
select grp
from y
where cnt = (select count(*) from x)
answered Mar 21 at 15:03
The ImpalerThe Impaler
11.4k41441
add a comment |
You can simulate database division. It's a little bit cumbersome but here it is:
with
x as (
select
from a
where a.list = 5
),
y as (
select grp, count(*) as cnt
from b
join x on x.item = b.item
group by grp
)
select grp
from y
where cnt = (select count(*) from x)
answered Mar 21 at 15:03
The ImpalerThe Impaler
11.4k41441
You can simulate database division. It's a little bit cumbersome but here it is:
with
x as (
select
from a
where a.list = 5
),
y as (
select grp, count(*) as cnt
from b
join x on x.item = b.item
group by grp
)
select grp
from y
where cnt = (select count(*) from x)
answered Mar 21 at 15:03
The ImpalerThe Impaler
11.4k41441
answered Mar 21 at 15:03
The ImpalerThe Impaler
11.4k41441
answered Mar 21 at 15:03
The ImpalerThe Impaler
11.4k41441
answered Mar 21 at 15:03
The ImpalerThe Impaler
11.4k41441
11.4k41441
add a comment |
add a comment |
Shawn Reever is a new contributor. Be nice, and check out our Code of Conduct.
Shawn Reever is a new contributor. Be nice, and check out our Code of Conduct.
Shawn Reever is a new contributor. Be nice, and check out our Code of Conduct.
Shawn Reever is a new contributor. Be nice, and check out our Code of Conduct.
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%2f55283274%2fsql-match-group-of-records-to-another-group-of-records%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
Please tag with the database you are using.
– Gordon Linoff
Mar 21 at 14:55
SQL does not natively implement table division. It can be simulated, however.
– The Impaler
Mar 21 at 14:58