Home How can I write a query to match groups of records
Reply: 0

How can I write a query to match groups of records

user1414 Published in June 19, 2018, 6:22 pm

This is a puzzling SQL task. I'd like to do it with a query instead of stepping through with a cursor and doing it the "hard way".

If I have two tables TableA and TableB each with a grouping column as below:

TableA             TableB
-------------      -------------
id     group       id     group
------ ------      ------ ------
1      D           1      X
2      D           2      X
3      D           3      Y
4      D           4      Y
4      E           5      Y
5      E           5      Z
5      F           6      Z

Note the group names are not the same name.

I want to know if a given TableB group is comprised entirely of IDs which are also grouped together in any group in TableA. The TableA group can have more IDs than the TableB group, so long as it has all of the same IDs as the TableB group. IDs can be in more than one group in either table.

From the tables above, I should find out that group X from TableB matches a group in TableA, but groups Y and Z do not.

I've tried many different queries, subqueries, recursive CTEs. I've only ended up with wrong results and headaches. The real dataset is significantly larger, so performance should be considered a factor too. Unfortunately, that means the cross-join solution proposed in an answer below won't work.

Is this even possible with SQL?

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.576867 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO