SQL SELECT Join?

Question

I have a MySql DB.

There is a table with products and orders with the structure:

• Products: product_id, name, manufacturers_id
• Orders: orders_id, product_id, quantity

Now I want to get all orders (show only products where product manufacturers_id=1). I tried:

SELECT
orders.orders_id,
orders.product_id
FROM products, orders
WHERE products.manufacturers_id = 1
GROUP BY orders_id
ORDER BY orders_id

But this doesn't work. What's wrong?

Sry for editing: someone else edited my post end deleted an essentiell part of the question

EDIT

To clarify my problem I added some new information:

I got an MySQL DB with MyISAM tables. The two relevant tables are:

• orders_products: orders_products_id, orders_id, product_id, product_name, product_price, product_name, product_model, final_price, ...
• products: products_id, manufacturers_id, ...

(for full information about the tables see screenshot products (Screenshot) and screenshot orders_products (Screenshot))

Now what I want is this: - Get all Orders who ordered products with manufacturers_id = 1. And the product name of the product of this order (with manufacturers_id = 1). Grouped by orders.

What I did so far is this:

SELECT
op.orders_id,
p.products_id,
op.products_name,
op.products_price,
op.products_quantity
FROM orders_products op , products p 
INNER JOIN products
ON op.products_id = p.products_id
WHERE p.manufacturers_id = 1 AND
p.orders_id > 10000

p.orders_id > 10000 for testing to get only a few order_id's. But thies query takes much time to get executed if it even works. Two times the sql server stucked. Where is the mistake?

Because of the confusing about my edits i opened a new more understandable post: SQL Inner Join : DB stuck

Answer 1

That should do it!

SELECT
*.orders
FROM orders INNER JOIN products
ON orders.product_id = products.product_id
WHERE products.manufacturers_id =1
ORDER BY orders_id

Answer 2

WHERE products.manufacturers_id = 1 AND products.product_id = orders.product_id

That's if you want to join the two tables. And besides that you request a product_od instead of a product_id

Answer 3

The simple way would be:

SELECT * FROM Orders WHERE product_id = 1