MySql Join Question

ccline · #1 (**permalink**) 04-15-04, 10:21

I have 3 tables that I have to work with. I have listed all the important information below.

Rights
Rights_Customer_ID
Rights_Product_ID

Product
Product_ID
Product_Customer_ID

Customer
Customer_ID

For every Rights row there is a related Product and Customer. The Customer in this context is really the Customer that the Rights row belongs too.

For every Product row there is a related Customer. Customer in this context is really the Author of the product.

I need to produce is a SELECT statement that can return all Rights rows with the related Products and for each Rights row to return the related Customer. In addition, have each Product return it's related Customer as the Author.

As you can see both the Rights and Product table have an ID for a Customer row but the Customer will not be the same for both.

I'm not sure how to do this with out changing the tables.

r937 · #2 (**permalink**) 04-15-04, 10:29

PHP Code:


			
select b.Customer_ID as BelongsTo

     , Product_ID

     , a.Customer_ID as Author

  from Rights 

inner

  join Customer b

    on Rights_Customer_ID = b.Customer_ID

inner

  join Product

    on Rights_Product_ID = Product_ID

inner

  join Customer a

    on Product_Customer_ID = a.Customer_ID

ccline · #3 (**permalink**) 04-15-04, 10:35

That was very fast reply ) Much appreciated.

Internet Services

Web Development

Digital Marketing

Consumer Tech