Styjun

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?

0

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

share|improve this question

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 | 

0

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

share|improve this question

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

share|improve this question

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.

ID | List# | Item
1 | 5 | A
2 | 5 | C
3 | 5 | B
4 | 6 | A
5 | 6 | D

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

share|improve this question

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.

share|improve this question

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

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

2 Answers
2

active

oldest

votes

0

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.

share|improve this answer

answered Mar 21 at 14:57

Gordon LinoffGordon Linoff

790k35314418

add a comment | 

0

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)

share|improve this answer

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.

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

Name

Email

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

Name

Email

Required, but never shown

0

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.

share|improve this answer

answered Mar 21 at 14:57

Gordon LinoffGordon Linoff

790k35314418

add a comment | 

0

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.

share|improve this answer

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.

share|improve this answer

answered Mar 21 at 14:57

Gordon LinoffGordon Linoff

790k35314418

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;

share|improve this answer

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

0

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)

share|improve this answer

answered Mar 21 at 15:03

The ImpalerThe Impaler

11.4k41441

add a comment | 

0

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)

share|improve this answer

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)

share|improve this answer

answered Mar 21 at 15:03

The ImpalerThe Impaler

11.4k41441

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)

share|improve this answer

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