JOIN and one-to-many-to-one

[Guest]

Hello,

I am having trouble creating a join between a one-to-many-to-one table
structure. I thought it was possible, but I just cannot figure out what to
do.

Table A - many
Tabe B - one
Table C - many

I join table A.theKey on to table B.ProductID.
So at this point everything works fine. However, this next step is my
problem.

Table b.productid has another column that I am interested in and is the
source of my trouble. Since table b is "one relationship table", I have
created another column (table b.likeProductID) in table b that allows me to
group product ids that are exactly alike.

So know I want to join table b.likeProductID with same product in Table
C.theKey. From here, I will return Table C.name.

I cannot seem to get this to work, and I am not sure what I am doing wrong.
Any help would be greatly appreciated!!!

Table Example

A.theKey B.productID B.LikeProductID C.theKey C.theName
--------- ------------- ------------------ ---------
-------------
478 89 89 89
Toy Train
345 232 232 232
Doll 478 345 232

232 478 89
89
345

So as I am scanning table A, I pull 478. I compare that to b.productId.
OK, a match exist so tell me it's "like" product. In this case 89 is it.
Then I go to table C, find 89, and finally return the tame....Toy Train!!!

Thanks for your help!!!

 

[Guest]

Does the following work?

SELECT A.theKey, C.theName
FROM A INNER JOIN (B INNER JOIN C ON B.likeProductId = C.theKey) ON A.theKey
= B.productId

Hope This Helps
Gerald Stanley MCSD

 

[Guest]

Thanks so much for the help. I am definetly getting closer. Now, one last
problem.

I used your SQL statement. The issue now is that column B.likeProductID has
multiple entries for it. Thus I am returning multiple records when I only
one to retrieve one. So if B.likeproductid has 5 records, 5 records are
returned even though the records are exactly the same. Again, thank you for
your help.

Best.

Does the following work?

SELECT A.theKey, C.theName
FROM A INNER JOIN (B INNER JOIN C ON B.likeProductId = C.theKey) ON A.theKey
= B.productId

Hope This Helps
Gerald Stanley MCSD

A.theKey B.productID B.LikeProductID C.theKey C.theName
--------- ------------- ------------------ ---------
-------------
478 89 89 89
Toy Train
345 232 232 232
Doll 478 345 232

232 478 89
89 345 232

 

[Guest]

Does the following work?

SELECT DISTINCT A.theKey, C.theName
FROM A INNER JOIN (B INNER JOIN C ON B.likeProductId = C.theKey) ON A.theKey
= B.productId

Hope This Helps
Gerald Stanley MCSD

 

[Guest]

Gerald,

Thanks so much. Everything is working fine now and I did not have to use
DISTINCT. Your first post solved the problem. I had typed in something
wrong that was returning the multiple records. I

This is the state:

FROM (dbo_deals INNER JOIN rmCParty ON dbo_deals.cparty =
rmCParty.rmCPartyQuantumID) INNER JOIN dbo_cparty ON rmCParty.rmCPartyLikeID
= dbo_cparty.thekey