Re: Many to Many Select???

Mary Assaad wrote:
> Hello all! I have three tables, tbcables, tbConnectors, and
> tbAssembly. tbAssembly is what connects tbCables and tbConnectors
> since there is a Many to Many relationship between Cables and
> Connectors. I want to select all the connectors that can be attached
> to a certain Cable. I've tried
>
> SELECT Connector FROM tbConnectors WHERE ConnectorID=(SELECT
> ConnectorID FROM tbAssembly WHERE CableID= (SELECT CableID FROM
> tbCables WHERE Cable="CableName")
>
> but this doesn't work b/c I get many ConnectorID's ie ("At most one
> record can be returned by this subquery. (Error 3354)). I'm guessing
> I need to do some kind of INNER JOIN but I'm not sure how to do it!
>
> any advice would be much appreciated!

Basic SQL to JOIN tables:

SELECT whatever you want
  FROM Table1 t1 JOIN Table2 t2 ON t1.key = t2.fkey JOIN Table3 t3 ON t2.key = t3.fkey ....
  WHERE other conditions;

For your case:
SELECT c.Connector
  FROM tbConnectors c JOIN tbAssembly a ON c.ConnectorID = a.ConnectorID

        JOIN tbCables cb ON a.CableID = cb.CableID   WHERE cb.Cable = 'CableName';

By the way: In standard SQL, a string is delimited by single quotes instead of double ones. But Access AFAIK, allows (or mandates?) double quotes.

hth,
Heinz Received on Mon Feb 10 2003 - 08:58:57 CET