CODEWITHSUNDEEP
mysqljoinassociations
user482594
user482594

Reputation: 17486

MySQL join Question

I am trying to achieve many-to-many relationship between db table 'Products' and table 'Companies'

Company has its unique id, and products have unique id as well, as their primary keys.

And lets say table 'Sells' has company_id, and product_id as columns. Since it is going to be a many-to-many relationship, there wouldn't be a primary key for this table, right?

What would Mysql query be like if I want to get the products that belongs to a single company? Can that be done in a single query?

Upvotes: 2

Views: 43

Answers (3)

chahuistle
chahuistle

Reputation: 2645

Since it is going to be a many-to-many relationship, there wouldn't be a primary key for this table, right?

The fact that there is a many-to-many relationship does not hinder you adding a primary key. You can generate one with an auto-id (Sale_Id). You should not rely on product_id + company_id, as a given company could sale the same product more than once.

What would Mysql query be like if I want to get the products that belongs to a single company? Can that be done in a single query?

SELECT S.PRODUCT_ID FROM SALES S INNER JOIN COMPANY C ON (C.ID = S.COMPANY_ID) WHERE C.ID = XXX

Upvotes: 0

Joe Stefanelli
Joe Stefanelli

Reputation: 135928

Personally, I would make a primary key of the composite (company_id, product_id) in the Sells table.

As for the queries themselves, if you only want product information returned:

select p.*
    from Sells s
        inner join products p
            on s.product_id = p.product_id
    where s.company_id = 123

If you want company and product information returned:

select c.*, p.*
    from Company c
        inner join Sells s
            on c.company_id = s.company_id
        inner join products p
            on s.product_id = p.product_id
    where c.company_id = 123

Upvotes: 1

CanSpice
CanSpice

Reputation: 35828

You can have a primary key made up of multiple columns, so for your joint table you'd make the primary key be (company_id, product_id).

Upvotes: 1

Related Questions