MySQL Inner Join Query Multiple Tables

join, mysql, sql

Solution

Try this:

SELECT 
  p.id,
  p.name,
  l.url,
  o.user_id,
  o.pricing_id
FROM orders AS o
INNER JOIN products_pricing AS pp ON o.pricing_id  = pp.id
INNER JOIN products         AS  p ON pp.product_id = p.id
INNER JOIN listings         AS  l ON l.user_id = o.user_id
WHERE o.user_id ='7' 
  AND l.id = 233 
  AND l.url = 'test.com';

SQL Fiddle Demo

For the sample data you posted in your question, this will give you:

| ID |        NAME |      URL | USER_ID | PRICING_ID |
------------------------------------------------------
| 33 | testproduct | test.com |       7 |         37 |

Problem

I am trying to join up a few tables and examples of the layouts are below: orders ``` user_id=7 pricing id=37 ``` products_pricing ``` id=37 product_id=33 ``` products ``` id=33 name=test product ``` SQL ``` SELECT * FROM orders INNER JOIN products_pricing ON orders.pricing_id = products_pricing.id INNER JOIN products ON products_pricing.product_id = products.id WHERE orders.user_id = '7' "); ``` listings ``` id=233 user_id=7 url=test.com ``` With this SQL i get an output giving me all the products from the user_id of 7 and it will list each products name in a while loop. However when I add another INNER JOIN for a table called listings, which has a user_id column and I need to grab a url for each row that matches so I can hyperlink the product names with the url I get everything contained in the listings table as well as the working stuff above. I'm either doing it very wrong or am missing something. I've spent a few hours trying to figure it out but keep getting the same result. Can anyone help me out?

Original source