Re: many to many

[Simon Garner]

On 21/04/2005 3:36 p.m., Perry Merritt wrote:
Hi, I'm a novice.
 
Hi Perry,
I've designed a database that supports many to many relationships (actually many to many to many) and I need help creating the query to find my data. I know things like JOINs exist, but don't have a clue how to use them.
 
I have three main tables and two link tables, The main tables are A, B, and C. Each are defined with id INT and word VARCHAR(32); The link tables are X and Y. X links A and B with the columns a_id and b_id. Y links the tables B and C with columns b_id and c_id.
 
Here's what I want to accomplish:
 
  Get the A.id where A.word = "some value"
  Use A.id to search X where X.a_id=A.id (from above)
   Use all occurences of X.a_id = A.id to select word from B using B.id=X.b_id
   AND finally,
   select C.id where C.Word = "Some other value"
    and given Y.c_id = C.id use the matching Y.b_id to further limit the select on B.word
 
> Can this convoluted mess be understood and if so, can a single query 
pull it off?
>

I'm not sure I completely understand what you're trying to do, but try 
the following:

SELECT
        A.id AS a_id,
        B.word AS b_word,
        C.id AS c_id
FROM
        A
LEFT JOIN X ON
        X.a_id=A.id
LEFT JOIN B ON
        B.id=X.b_id
LEFT JOIN Y ON
        Y.b_id=B.id
LEFT JOIN C ON
        C.id=Y.c_id
WHERE
        A.word="some value"
        AND C.word="some other value"
I haven't tested this but I think it would work...

I'm implementing this in perl, so I can break the queries into pieces if I absolutely have to.
 
Thanks
>
-Simon
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]