CODEWITHSUNDEEP
mysqldatabase
Amer
Amer

Reputation: 335

MYSQL query of one table that is a many to many relationship to another table

I have a table in MySQL of products that were created in different years. attributes include id, name and year. I have another table that has a relation ship between products "DependentProduct" that has attributes id, p1id, and p2id such that product with id=p2id depends on product with id=p1id. I am trying to find out for all products created in year 2000, how many of the products created in year 2001 depend on them. so if i have 10 products created in 2000, and 20 products created in 2001, I would like to get something like this:

pid    2001
1       5
2       10  
3       9
.       .
.       .
10      3

meaning that 5 of the products created in 2001 depend on pid 1, which is a product created in 2000. it could also be the case that some products that were created in 2001 depend on many products in year 2000.

Thanks a lot for your help

Upvotes: 1

Views: 76

Answers (1)

Johan
Johan

Reputation: 76753

Use a join

SELECT t2.* FROM table1 t1
INNER JOIN table2 t2 ON (t1.id = t2.tbl1_id)
WHERE t1.date = '2011-08-20'

Upvotes: 1

Related Questions