Hi, 


I am looking to pick up the relationship between items in a 
cross-reference table. For example, searching for c OR f (bracketed 
below to highlight them), I want to list the other right hand side keys 
that are linked by the left hand side key. So in the below case, "a" is 
linked 3 times due to (c), b is linked twice due to (c), d is linked 
twice due to (c) and (c OR f) and finally e, linked once due to (c). 


1 | a 
1 | b 
1 | (c) 
1 | d 
2 | a 
2 | b 
2 | (c) 
3 | z 
4 | a 
4 | (c) 
5 | (c) 
5 | e 
6 | (f) 
6 | (c) 
6 | d 


Just wondering what the best way of doing this is, and how the result 
can be ranked by the number of hits (so a,b,d,e) and how to get the 
number of links (3, 2, 2, 1) 


Hope this is clear. This has had me scratching my head for a while :/ 


Many thanks in advance!

---

Clarification of Question by goose_man-ga on 03 Feb 2006 08:53 PST

Oops - Forgot to mention that I am looking to do this using MySQL. Sorry!!

The assumption seems to be the links are bi-directional.  If you
reform the problem:

1 | a 
1 | b 
1 | (c) 
1 | d , etc

as a list of one-way associations:

a -> b
a -> c
a -> d
b -> a
b -> c 
b -> d
c -> a
c -> b
c -> d
d -> a
d -> b
d -> c, etc

then put all the one-way associations into a std::multimap<char,char>
(assuming you use C++).  If using C, Pascal, etc., an array of
struct/records/etc like:

struct {
    char from;
    char to;
} one_way_association;

will work in a similar way (std::vector<one_way_association> in C++ if you prefer).

A std::multimap will automatically put the from-association in order
(to-association might not necessarily be in order by default though). 
You can use an appropriate sort() function to put them in order from
C, Pascal, etc.

Then,

std::multimap<char,char> associations;
pair<blah,blah> range = associations.equal_range( 'c' );
int number_of_items = distance( range.first, range.second );

will let you find out how many items 'c' is linked to.  It should be
relatively simple whatever associations you want from here.

same goes for MySQL.  Reform the tables as a list of one-way
associations and bung in your query:

SELECT COUNT( from ) FROM associations WHERE from='c';

If you need to have the table format you specified, I believe you will
need to join the table to itself.  If I remember correctly (from doing
a similar thing in Oracle), you have to give temporary names to the
table, like:

SELECT COUNT( from.char ) FROM associationlist=from,
associationlist=to where from.linkid=tolinkid;

or whatever the proper renaming syntax is.

That sounds like a great approach, but as I am new to MySQL, I am
struggling with the syntax. Does anyone have advice on how to
implement this fully in MySQL?