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:[EMAIL PROTECTED]
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/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
