1

If I have one table that references the names of sponsors and the product ids of the products they recommend, as such:

--------------------------------
|Name |Product ID 1|Product ID 2|
--------------------------------
|Jon  |     1      |      3     |
|Sally|     1      |      2     |
--------------------------------

And another table that lists the products:

----------------------------------------
|Product ID |Product Name|Product Price|
----------------------------------------
|     1     |  Prod 1    |    25       |
|     2     |  Prod 2    |    35       |
|     3     |  Prod 3    |    45       |
----------------------------------------

How do I join these together so that I have the name of sponsor plus each product name and product price that they recommend? INNER JOIN and LEFT JOIN only seem to pull through one of the products, but not all of them.

Strawberry
  • 33,750
  • 13
  • 40
  • 57
Ryan
  • 767
  • 3
  • 9
  • 31
  • 1
    possible duplicate : http://stackoverflow.com/questions/3709560/mysql-join-three-tables – Towfik Alrazihi Sep 01 '16 at 16:14
  • 1
    @TowfikAlrazihi He's only joining 2 tables, not 3 tables. – Barmar Sep 01 '16 at 16:15
  • Answer here :http://www.yourwebskills.com/mysqljoin2.php – Towfik Alrazihi Sep 01 '16 at 16:18
  • From the looks of it these tables are nowhere near normalized. You should partition entities. Unless the two products in your sponsors table are related -- that is, every sponsor will always have exactly two products -- you should have a sponsors table, a product table, and recommendations table. – Charles D Pantoga Sep 01 '16 at 16:49
  • `sponsors table: sponsor_id, sponsor_name, email, location, etc...` => `vendor table: vendor_id, vendor_name, etc...` => `products table: product_id, vendor_id, product_name, price, etc...` => `recommendations table: recommendation_id, sponsor_id, product_id` – Charles D Pantoga Sep 01 '16 at 16:52
  • This way all your recommendations are in one place. All your products are in one place. All your sponsors are in one place. All your vendors are in one place. You can search by product, vendor, sponsor, or recommendation. You can easily query your most recommended product, or show sponsors other products by vendors they've recommended already, – Charles D Pantoga Sep 01 '16 at 16:54

1 Answers1

1

Join twice.

SELECT s.name, p1.ProductName AS product_1_name, p1.ProductPrice AS product_1_price, p2.ProductName AS product_2_name, p2.ProductPrice AS product_2_price
FROM sponsers AS s
JOIN products AS p1 ON s.ProductID1 = p1.ProductID
JOIN products AS p2 ON s.ProductID2 = p2.ProductID
Barmar
  • 741,623
  • 53
  • 500
  • 612
  • Where do `product_1_name` and `product_2_name` factor into it? – Ryan Sep 01 '16 at 16:31
  • Those are aliases for the result columns, so you can access both products that they recommend. – Barmar Sep 01 '16 at 16:33
  • Why does your `sponsors` table have multiple columns for the products they recommend? What happens if they want to recommend more than 2 products? Wouldn't it be better to have a row for each product that they recommend? – Barmar Sep 01 '16 at 16:34
  • You're right, it probably should be like that. Also, if I add a second join, I get back an empty object. One join works fine, the second however does not. – Ryan Sep 01 '16 at 16:39
  • Strike that, if I use s.name, b.productName and c.ProductName as opposed to b1 and b2, I get the desired result. – Ryan Sep 01 '16 at 16:42
  • `b1` and `b2` should work just fine. You must have mistyped something. – Barmar Sep 01 '16 at 16:44