Comparing Collections - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Comparing Collections

Author

Topic

so1omons
Starting Member

2 Posts

Posted - 2002-01-31 : 00:55:44

Have a unique problem. I have two tables ... say books and buyers
BOOKS
|bkid| domainid|
|bk-1| 1|
|bk-1| 2|
|bk-1| 3|
|bk-2| 2|
|bk-2| 1|
|bk-3| 2|

Buyer
|byid|domainid|
|by-1| 1|
|by-1| 2|
|by-1| 3|
|by-2| 2|
|by-2| 3|
|by-2| 7|
|by-3| 2|

Now I want to find a perfect match for all buyers domainid and books domainid like for "by-1" domain ids(1,2,3) get books that match all the domainids like in the example book "bk-1" is the only one matching domainids 1,2 and 3.

Can this match be done using a single select query .. however complex .. or is there another way to do this .. pls dont tell me the design is shitty cos i know that already :(
If u want more details plz let me know

Thanx Solomons

Heard on the streets these days "He's not that wise after all is he?"

Nazim
A custom title

1408 Posts

Posted - 2002-01-31 : 01:26:36

if you know it, y dont you work on it. Sitting on a bad design is as good as sitting on a bomb which can explode anytime.

Give Top Priority to your design improvement.

quote:

pls dont tell me the design is shitty cos i know that already :(

Coming to your question
if i have understood it correctly this should do it.

select byid from
buyer
group by buyid
having count(distinct domainid)=( select count(distinct domainid) From Books)

--------------------------------------------------------------
Dont Tell God how big your Problem is , Tell the Problem how Big your God is

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-01-31 : 05:07:46

The design looks fine from here. I think Nazim has interpreted your question differently, but the way I read it was that you wanted the combinations of buyers and books such that the set of buyer's domains = set of book's domains. I can't help thinking this is a sub-optimal solution, but it does work:


SELECT byid, bkid
FROM (
    SELECT b1.byid, b1.domainid, COUNT(*) dcount
    FROM Buyer b1 INNER JOIN Buyer b2 ON b1.byid = b2.byid
    GROUP BY b1.byid, b1.domainid) Buyer
INNER JOIN (
    SELECT b1.bkid, b1.domainid, COUNT(*) dcount
    FROM BOOKS b1 INNER JOIN BOOKS b2 ON b1.bkid = b2.bkid
    GROUP BY b1.bkid, b1.domainid) Books
  ON Buyer.domainid = Books.domainid AND Buyer.dcount = Books.dcount
GROUP BY byid, bkid
HAVING COUNT(*) = MAX(Books.dcount)

Hmm. Perhaps better to flatten out the subqueries: that way the optimizer seems to be able to build a plan without sorts (assuming pks on Buyers (byid, domainid) and BOOKS (bkid, domainid)). See if this is any better (or worse!)


SELECT by1.byid, bk1.bkid
FROM Buyer by1
INNER JOIN BOOKS bk1 ON by1.domainid = bk1.domainid
INNER JOIN (
  SELECT byid, COUNT(*) bycount
  FROM Buyer
  GROUP BY byid) by2 ON by1.byid = by2.byid
INNER JOIN (
  SELECT bkid, COUNT(*) bkcount
  FROM BOOKS
  GROUP BY bkid) bk2 ON bk1.bkid = bk2.bkid AND bycount = bkcount
GROUP BY by1.byid, bk1.bkid
HAVING COUNT(*) = MAX(bycount)

Edited by - Arnold Fribble on 01/31/2002 05:48:30

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-01-31 : 06:08:41

If you have a small number of domains (numeric, preferably contiguous), you can treat the domains as a bit vector like this:


SELECT byid, bkid
FROM Buyer
CROSS JOIN BOOKS
GROUP BY byid, bkid
HAVING SUM(POWER(2, Buyer.domainid)) = SUM(POWER(2, BOOKS.domainid))


SELECT byid, bkid
FROM (
  SELECT byid, SUM(POWER(2, domainid)) bydomains
  FROM Buyer
  GROUP BY byid) Buyer
INNER JOIN (
  SELECT bkid, SUM(POWER(2, domainid)) bkdomains
  FROM BOOKS
  GROUP BY bkid) BOOKS ON bydomains = bkdomains

May need some bigint casts in there if your domainid values are in the 32-63 range.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources