Relational division

re: Relational division

Adam Machanic — Wed, 30 Jun 2010 20:11:52 GMT

I think you forgot to mention what the problem is?

re: Relational division

Peso — Wed, 30 Jun 2010 20:51:34 GMT

Oh, I thought the title said it all.

The problem is to find an efficient query to deal with relational algebra.

Mr Celko has a wonderful description of Relational Division and some examples here http://www.simple-talk.com/sql/t-sql-programming/divided-we-stand-the-sql-of-relational-division/

The problem is that they are not very efficient queries. It's all about to find group of connected records which is evenly divided by a number of wanted records.

For the example above, to find the ParentID(s) which have exactly ONE, TWO and THREE as keywords. Not less and certainly not more.

re: Relational division

Adam Machanic — Fri, 02 Jul 2010 14:22:05 GMT

A more interesting challenge--and much closer to what Celko has done--is to make this work with TWO tables. The one you already have, plus:

DECLARE @Sample2 TABLE

(

KeyID INT NOT NULL,

Keyword VARCHAR(25) NOT NULL,

UNIQUE (KeyID, Keyword)

)

INSERT @Sample2

VALUES (6, 'one'),

(6, 'two'),

(6, 'three'),

(6, 'four'),

(7, 'one'),

(7, 'two'),

(7, 'three'),

(8, 'one'),

(8, 'two')

Then the output of the division query would be:

ParentID -- KeyID

1 6

2 7

3 8

re: Relational division

Adam Machanic — Fri, 02 Jul 2010 14:28:06 GMT

Actually there should probably be some invalid stuff in there too:

INSERT @Sample2

VALUES (6, 'one'),

(6, 'two'),

(6, 'three'),

(6, 'four'),

(7, 'one'),

(7, 'two'),

(7, 'three'),

(8, 'one'),

(8, 'two'),

(9, 'one'),

(9, 'two'),

(9, 'four')

re: Relational division

Peso — Fri, 02 Jul 2010 15:17:45 GMT

Already ahead of you. Posted here

http://weblogs.sqlteam.com/peterl/archive/2010/07/02/Proper-Relational-Division-With-Sets.aspx

re: Relational division

Adam Machanic — Fri, 02 Jul 2010 15:42:14 GMT

Why do you only cross post certain things and then not the followup? Seems counter productive--and it's impossible for readers to keep track.

re: Relational division

Naomi — Mon, 09 Aug 2010 16:18:38 GMT

Agree with the last comment - post the whole solution with as many needed cross references in one blog.