Welcome to the Treehouse Community

Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.

Start your free trial

Databases

Tadjiev Codes
Tadjiev Codes
9,626 Points
Posted by Tadjiev Codes
Tadjiev Codes
9,626 Points

Inner Join and Self Join explanation

Dear Treehouse Community,

-- Question 4
--List the product name and vendor id for all products that we have purchased from more than one vendor, 
--sort this one on product name (Hint: you’ll need to use a Self-Join and an additional INNER JOIN to solve, see above for reference)
-- Don’t forget to remove any duplicates. Partial listing shown:
-- Answer 4

SELECT DISTINCT p.name AS product, v.id AS Vendorid
FROM products p 
     INNER JOIN product_vendors pv ON p.id = pv.productid
     INNER JOIN product_vendors pv2 ON pv.productid = pv2.productid AND pv.vendorid != pv2.vendorid 
     INNER JOIN vendors v ON v.id = pv.vendorid
ORDER BY p.name;

I just wanted to ask why do we need this part where its not equal !=

pv.vendorid != pv2.vendorid

I guess to remove the repetition of the products and why we need this Self Joins? Thanks

1 Answer

Steven Parker
Steven Parker
231,186 Points
Steven Parker
Steven Parker
231,186 Points

That's to enforce the criteria of "purchased from more than one vendor". If all the purchases are from the same vendor, there would be no rows with the same product id but unequal vendor id's.

The repetitions are removed by the DISTINCT keyword.