Finding distinct records from an intersection of sets of data

Folks,

I have hit a wall in my SQL knowledge. I need to do the following, find =
the sessions where I have seen all the following pages from a set.

From my example below, I should expect:
Session | Set_Name
---------+----------
1 | set 2
---------+----------
1 | set 3
---------+----------
2 | set 1
---------+----------
2 | set 2
---------+----------
2 | set 3
---------+----------
4 | set 1
---------+----------
4 | set 3
---------+----------

Man, it has been pulling teeth to get this to work correctly. I expect =
that I will either need to use an ALL or [NOT] EXISTS condition but I =
have yet to get the syntax correct to figure it out correctly. If =
anyone can help I would greatly appreciate it.

Thanks,

Mark

###############################
CREATE TEMPORARY TABLE session_test (
Session_ID INT unsigned NOT NULL default 0,
Page CHAR(1) NOT NULL default 'A');

CREATE TEMPORARY TABLE page_set (
Set_Name CHAR(6) NOT NULL default 'set 0',
Page CHAR(1) NOT NULL default 'A');

INSERT INTO session_test
VALUES
(1,'a'),(1,'c'),(1,'d'),(1,'f'),(1,'h'),(1,'q'),(1,'n'),(1,' e'),(1,'a'),
(2,'a'),(2,'b'),(2,'c'),(2,'f'),(2,'e'),(2,'h'),(2,'n'),
(3,'b'),(3,'c'),(3,'b'),(3,'d'),(3,'c'),(3,'h'),(3,'c'),(3,' n'),
(4,'a'),(4,'c'),(2,'b'),(2,'n');

INSERT INTO page_set
VALUES
('set 1','a'),('set 1','b'),('set 1','c'),
('set 2','a'),('set 2','h'),('set 2','n'),
('set 3','a'),('set 3','n');
###############################

--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=3Dgcdmw-win32@m.gmane.org

RE: Finding distinct records from an intersection of sets of data

I think your "INSERT INTO session_test ..." should end
"(4,'b'),(4,'n');" for your expected result to be true.

The following query seems to give the right answer

SELECT Session_ID, t1.Set_Name, count(t3.Page) AS SessCount,
(SELECT COUNT(Page) FROM page_set AS t4 WHERE t4.Set_Name =3D
t1.Set_Name) AS SetCount
FROM page_set AS t1 INNER JOIN
(SELECT DISTINCT * FROM session_test) AS t3 ON t1.Page =3D t3.Page
GROUP BY Session_ID, Set_Name
HAVING SessCount =3D SetCount

It essentially makes sure the number of distinct pages from a set in a
session match the number of pages in the set.

John Bonnett


-----Original Message-----
From: Mark Smith [mailto:Mark.Smith@keynote.com]=20
Sent: Saturday, 25 June 2005 8:04 AM
To: win32@lists.mysql.com
Subject: Finding distinct records from an intersection of sets of data

Folks,

I have hit a wall in my SQL knowledge. I need to do the following, find
the sessions where I have seen all the following pages from a set.

From my example below, I should expect:
Session | Set_Name
---------+----------
1 | set 2
---------+----------
1 | set 3
---------+----------
2 | set 1
---------+----------
2 | set 2
---------+----------
2 | set 3
---------+----------
4 | set 1
---------+----------
4 | set 3
---------+----------

Man, it has been pulling teeth to get this to work correctly. I expect
that I will either need to use an ALL or [NOT] EXISTS condition but I
have yet to get the syntax correct to figure it out correctly. If
anyone can help I would greatly appreciate it.


Thanks,

Mark

###############################
CREATE TEMPORARY TABLE session_test (
Session_ID INT unsigned NOT NULL default 0,
Page CHAR(1) NOT NULL default 'A');

CREATE TEMPORARY TABLE page_set (
Set_Name CHAR(6) NOT NULL default 'set 0',
Page CHAR(1) NOT NULL default 'A');

INSERT INTO session_test
VALUES
(1,'a'),(1,'c'),(1,'d'),(1,'f'),(1,'h'),(1,'q'),(1,'n'),(1,' e'),(1,'a'),
(2,'a'),(2,'b'),(2,'c'),(2,'f'),(2,'e'),(2,'h'),(2,'n'),
(3,'b'),(3,'c'),(3,'b'),(3,'d'),(3,'c'),(3,'h'),(3,'c'),(3,' n'),
(4,'a'),(4,'c'),(2,'b'),(2,'n');

INSERT INTO page_set
VALUES
('set 1','a'),('set 1','b'),('set 1','c'),
('set 2','a'),('set 2','h'),('set 2','n'),
('set 3','a'),('set 3','n');
###############################


--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=3Dgcdmw-win32@m.gmane.org