Re: Many to Many Select???
Date: Mon, 10 Feb 2003 08:58:57 +0100
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:
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.
Heinz Received on Mon Feb 10 2003 - 08:58:57 CET