List:General Discussion« Previous MessageNext Message »
From:Rolando Edwards Date:December 1 2008 8:18pm
Subject:RE: Intersect question
View as plain text  
This one was complicated but here is what you want and the three different sets of test
data to prove it (cut and paste this code as is into MySQL and see the desired results):

USE test
DROP TABLE IF EXISTS SCHOOL;
CREATE TABLE SCHOOL (study VARCHAR(10),symbol CHAR(1), KEY StudySymbolIndex
(study,symbol));
INSERT INTO SCHOOL VALUES ('a2008','A'), ('a2008','B'), ('a2008','C'), ('a2008','D'),
('b2005','A'), ('b2005','B'), ('b2005','E');
ALTER TABLE SCHOOL ORDER BY study,symbol;
SELECT GROUP_CONCAT(DISTINCT study) AllStudies FROM SCHOOL;
SELECT X.* FROM (SELECT A.symbol,GROUP_CONCAT(B.study) Studies FROM (SELECT DISTINCT
symbol FROM SCHOOL ORDER BY symbol) A,SCHOOL B WHERE A.symbol=B.symbol GROUP BY A.symbol)
X, (SELECT GROUP_CONCAT(DISTINCT study) AllStudies FROM SCHOOL) Y WHERE
X.Studies=Y.AllStudies;

USE test
DROP TABLE IF EXISTS SCHOOL;
CREATE TABLE SCHOOL (study VARCHAR(10),symbol CHAR(1), KEY StudySymbolIndex
(study,symbol));
INSERT INTO SCHOOL VALUES ('a2008','A'), ('a2008','B'), ('a2008','C'), ('a2008','D'),
('a2007','A'), ('a2007','B'), ('a2007','D'), ('b2005','A'), ('b2005','B'), ('b2005','E');
ALTER TABLE SCHOOL ORDER BY study,symbol;
SELECT GROUP_CONCAT(DISTINCT study) AllStudies FROM SCHOOL;
SELECT X.* FROM (SELECT A.symbol,GROUP_CONCAT(B.study) Studies FROM (SELECT DISTINCT
symbol FROM SCHOOL ORDER BY symbol) A,SCHOOL B WHERE A.symbol=B.symbol GROUP BY A.symbol)
X, (SELECT GROUP_CONCAT(DISTINCT study) AllStudies FROM SCHOOL) Y WHERE
X.Studies=Y.AllStudies;
USE test
DROP TABLE IF EXISTS SCHOOL;
CREATE TABLE SCHOOL (study VARCHAR(10),symbol CHAR(1), KEY StudySymbolIndex
(study,symbol));
INSERT INTO SCHOOL VALUES ('a2008','A'), ('a2008','B'), ('a2008','C'), ('a2008','D'),
('a2007','A'), ('a2007','B'), ('a2007','D'), ('b2006','A'), ('b2006','B'), ('b2006','F'),
('b2006','G'), ('b2006','H'), ('b2005','A'), ('b2005','B'), ('b2005','E'); ALTER TABLE
SCHOOL ORDER BY study,symbol;
SELECT GROUP_CONCAT(DISTINCT study) AllStudies FROM SCHOOL;
SELECT X.* FROM (SELECT A.symbol,GROUP_CONCAT(B.study) Studies FROM (SELECT DISTINCT
symbol FROM SCHOOL ORDER BY symbol) A,SCHOOL B WHERE A.symbol=B.symbol GROUP BY A.symbol)
X, (SELECT GROUP_CONCAT(DISTINCT study) AllStudies FROM SCHOOL) Y WHERE
X.Studies=Y.AllStudies;

Here are the results from my console:

mysql> USE test
Database changed
mysql> DROP TABLE IF EXISTS SCHOOL;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE SCHOOL (study VARCHAR(10),symbol CHAR(1),
    -> KEY StudySymbolIndex (study,symbol)); Query OK, 0 rows affected (0.06 sec)

mysql> INSERT INTO SCHOOL VALUES
    -> ('a2008','A'),
    -> ('a2008','B'),
    -> ('a2008','C'),
    -> ('a2008','D'),
    -> ('b2005','A'),
    -> ('b2005','B'),
    -> ('b2005','E');
Query OK, 7 rows affected (0.00 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE SCHOOL ORDER BY study,symbol;
Query OK, 7 rows affected (0.03 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> SELECT GROUP_CONCAT(DISTINCT study) AllStudies FROM SCHOOL;
+-------------+
| AllStudies  |
+-------------+
| a2008,b2005 |
+-------------+
1 row in set (0.00 sec)

mysql> SELECT X.* FROM
    -> (SELECT A.symbol,GROUP_CONCAT(B.study) Studies FROM
    -> (SELECT DISTINCT symbol FROM SCHOOL ORDER BY symbol) A,SCHOOL B
    -> WHERE A.symbol=B.symbol GROUP BY A.symbol) X,
    -> (SELECT GROUP_CONCAT(DISTINCT study) AllStudies FROM SCHOOL) Y
    -> WHERE X.Studies=Y.AllStudies;
+--------+-------------+
| symbol | Studies     |
+--------+-------------+
| A      | a2008,b2005 |
| B      | a2008,b2005 |
+--------+-------------+
2 rows in set (0.00 sec)

mysql> USE test
Database changed
mysql> DROP TABLE IF EXISTS SCHOOL;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE SCHOOL (study VARCHAR(10),symbol CHAR(1),
    -> KEY StudySymbolIndex (study,symbol)); Query OK, 0 rows affected (0.05 sec)

mysql> INSERT INTO SCHOOL VALUES
    -> ('a2008','A'),
    -> ('a2008','B'),
    -> ('a2008','C'),
    -> ('a2008','D'),
    -> ('a2007','A'),
    -> ('a2007','B'),
    -> ('a2007','D'),
    -> ('b2005','A'),
    -> ('b2005','B'),
    -> ('b2005','E');
Query OK, 10 rows affected (0.00 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE SCHOOL ORDER BY study,symbol;
Query OK, 10 rows affected (0.03 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> SELECT GROUP_CONCAT(DISTINCT study) AllStudies FROM SCHOOL;
+-------------------+
| AllStudies        |
+-------------------+
| a2007,a2008,b2005 |
+-------------------+
1 row in set (0.00 sec)

mysql> SELECT X.* FROM
    -> (SELECT A.symbol,GROUP_CONCAT(B.study) Studies FROM
    -> (SELECT DISTINCT symbol FROM SCHOOL ORDER BY symbol) A,SCHOOL B
    -> WHERE A.symbol=B.symbol GROUP BY A.symbol) X,
    -> (SELECT GROUP_CONCAT(DISTINCT study) AllStudies FROM SCHOOL) Y
    -> WHERE X.Studies=Y.AllStudies;
+--------+-------------------+
| symbol | Studies           |
+--------+-------------------+
| A      | a2007,a2008,b2005 |
| B      | a2007,a2008,b2005 |
+--------+-------------------+
2 rows in set (0.00 sec)

mysql> USE test
Database changed
mysql> DROP TABLE IF EXISTS SCHOOL;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE SCHOOL (study VARCHAR(10),symbol CHAR(1),
    -> KEY StudySymbolIndex (study,symbol)); Query OK, 0 rows affected (0.05 sec)

mysql> INSERT INTO SCHOOL VALUES
    -> ('a2008','A'),
    -> ('a2008','B'),
    -> ('a2008','C'),
    -> ('a2008','D'),
    -> ('a2007','A'),
    -> ('a2007','B'),
    -> ('a2007','D'),
    -> ('b2006','A'),
    -> ('b2006','B'),
    -> ('b2006','F'),
    -> ('b2006','G'),
    -> ('b2006','H'),
    -> ('b2005','A'),
    -> ('b2005','B'),
    -> ('b2005','E');
Query OK, 15 rows affected (0.00 sec)
Records: 15  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE SCHOOL ORDER BY study,symbol;
Query OK, 15 rows affected (0.03 sec)
Records: 15  Duplicates: 0  Warnings: 0

mysql> SELECT GROUP_CONCAT(DISTINCT study) AllStudies FROM SCHOOL;
+-------------------------+
| AllStudies              |
+-------------------------+
| a2007,a2008,b2005,b2006 |
+-------------------------+
1 row in set (0.02 sec)

mysql> SELECT X.* FROM
    -> (SELECT A.symbol,GROUP_CONCAT(B.study) Studies FROM
    -> (SELECT DISTINCT symbol FROM SCHOOL ORDER BY symbol) A,SCHOOL B
    -> WHERE A.symbol=B.symbol GROUP BY A.symbol) X,
    -> (SELECT GROUP_CONCAT(DISTINCT study) AllStudies FROM SCHOOL) Y
    -> WHERE X.Studies=Y.AllStudies;
+--------+-------------------------+
| symbol | Studies                 |
+--------+-------------------------+
| A      | a2007,a2008,b2005,b2006 |
| B      | a2007,a2008,b2005,b2006 |
+--------+-------------------------+
2 rows in set (0.00 sec)

No matter how many distinct study values, you get the intersection of symbols.

GIVE IT A TRY AND HAVE FUN WITH IT !!!

-----Original Message-----
From: Micah Stevens [mailto:micah@stripped] 
Sent: Monday, December 01, 2008 2:07 PM
To: Andrej Kastrin
Cc: mysql
Subject: Re: Intersect question

On 12/01/2008 08:30 AM, Andrej Kastrin wrote:
> I have the table 'test' which includes two columns: 'study' and 'symbol':
>
> study symbol
> a2008 A
> a2008 B
> a2008 C
> a2008 D
> b2005 A
> b2005 B
> b2005 E
>
>
> The task is to perform an intersection on 'name' column according to
> all distinct values in 'study' column. During the experiments the
> intersection was done 'manually' using the query:
>
> SELECT a.symbol FROM test as a, test as b WHERE a.symbol=b.symbol and
> a.study="a2008" and b.study="b2005";
>
> So the result of the query above is (A, B).
>
> The question is how to implement this query more automatically,
> without directly referencing to the study names, because I want to
> implement it into a php script.
>
> Thank you in advance for any suggestions.
>
> Best, Andrej
>
Why not:

SELECT a.symbol FROM test as a, test as b WHERE a.symbol=b.symbol and
a.study != b.study group by symbol;


-Micah

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=1

Thread
Intersect questionAndrej Kastrin1 Dec
  • Re: Intersect questionMicah Stevens1 Dec
    • MySQl and LVMShain Miley1 Dec
      • Re: MySQl and LVMSimon J Mudd2 Dec
        • Re: MySQl and LVMShain Miley2 Dec
    • RE: Intersect questionRolando Edwards1 Dec
  • Re: Intersect questionMicah Stevens1 Dec
  • Re: Intersect questionPeter Brawley1 Dec