Hello,
I have a table containing article ids linked to the entity ids :
CREATE TABLE `eid_article_xref` (
`eid` varchar(5),
`aid` varchar(5),
KEY `eid` (`eid`),
KEY `aid` (`aid`)
) ENGINE=MyISAM
insert into eid_article_xref values ('X','a');
insert into eid_article_xref values ('X','b');
insert into eid_article_xref values ('Y','a');
insert into eid_article_xref values ('Z','b');
insert into eid_article_xref values ('T','a');
insert into eid_article_xref values ('U','a');
mysql> select * from eid_article_xref;
+------+------+
| eid | aid |
+------+------+
| X | a |
| X | b |
| Y | a |
| Z | b |
| T | a |
| U | a |
+------+------+
6 rows in set (0.00 sec)
I want to know the list of eid that share the same aid as (eid='X' and eid='Y') or same as (eid='X') or same as (eid='Y'), as well as the number of aid that they share.
This is the result I need:
+------+------+------+-----------+
| eid1 | eid2 | eid3 | count(aid)|
+------+------+------+-----------+
| X | Y | T | 1 |
| X | Y | U | 1 |
| X | | Z | 1 |
+------+------+------+-----------+
I found how to get these results, but I would like to improve it.
My current way to acheive this is to create a table, and insert rows from 3 different queries.
Here are the steps:
#1. Create a table that will hold the results. use unique key to get rid of unwanted rows:
create table associations
(eid1 varchar(5), eid2 varchar(5), eid3 varchar(5), count integer,
UNIQUE INDEX(eid1, eid3), UNIQUE INDEX(eid2, eid3));
#2. Get list of eid sharing same aid as eid=X and eid=Y
insert ignore into associations
SELECT a.eid as eid1, b.eid as eid2, c.eid as eid3, count(distinct(a.aid)) as count
FROM eid_article_xref a LEFT JOIN eid_article_xref b on a.aid=b.aid and a.eid='X' and b.eid='Y'
JOIN eid_article_xref c ON b.aid=c.aid
WHERE a.eid <> b.eid AND b.eid <> c.eid AND a.eid <> c.eid
GROUP BY c.eid
#3. Get list of eid sharing same aid as eid=X
insert ignore into associations
SELECT a.eid as eid1,'' as eid2, c.eid as eid3, count(distinct(a.aid)) as count
FROM eid_article_xref a
JOIN eid_article_xref c ON a.aid=c.aid and a.eid='X'
WHERE a.eid <> c.eid and a.eid <> 'Y' and c.eid <> 'Y' and c.eid not in (select eid3 from associations where eid2='X')
GROUP BY c.eid
#4. Get list of eid sharing same aid as eid=Y
insert ignore into associations
SELECT a.eid as eid1, '' as eid2, c.eid as eid3, count(distinct(a.aid)) as count
FROM eid_article_xref a
JOIN eid_article_xref c ON a.aid=c.aid and a.eid='Y'
WHERE a.eid <> c.eid and a.eid <> 'X' and c.eid <> 'X' and c.eid not in (select eid3 from associations where eid2='Y')
GROUP BY c.eid
#4. And here are the results I want !!
select * from associations;
+------+------+------+-------+
| eid1 | eid2 | eid3 | count |
+------+------+------+-------+
| X | Y | T | 1 |
| X | Y | U | 1 |
| X | | Z | 1 |
+------+------+------+-------+
3 rows in set (0.00 sec)
#Delete the table (to not generate too many of these tables if the process is repeted several times)
drop table associations;
... so, it works, but I am sure there is a better (more efficient and simpler) way to do all this!!
Thank you very much for you help!
Edited 1 time(s). Last edit at 11/19/2008 02:26AM by amelie a.