Re: Many to Many Select???

From: Bradley Crockett <crockett_at_horizon.bc.ca>
Date: Fri, 07 Feb 2003 17:58:01 GMT
Message-ID: <4ps74vghrvitagtbfr25fcov8q20dttrtv@4ax.com>

Hi, Mary.

I'm not sure about all your column names, but here goes...

SELECT Co.ConnectorName
FROM tbConnectors Co
 INNER JOIN tbAssembly A ON Co.ID = A.ConnectorID  INNER JOIN tbCables Ca On Ca.ID = A.CableID WHERE Ca.CableName = "Your_Cable_Name"

Bradley

On 7 Feb 2003 08:27:29 -0800, maryassaad_at_hotmail.com (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!
>thank you so much!
>mary

---

Bradley Crockett
Duncan BC Canada Received on Fri Feb 07 2003 - 11:58:01 CST