Retrieving data from multiple tables

Question

I need to get data from several tables and put them in a subformat.

SubForm columns are “product name” and “quantity”, and they should list products related to the order ID.

Relevant tables:

PRODUCTS(productID,productName)

ORDER(orderID,prodID,quantity)

Where the prodID in ORDER refers to the PRODUCT table.

As you can see, the problem is that the product name is in another table in order. So, I need to get the following data:

Products(productName)

Order(quantity)

Regarding orderID .

How can I use a SQL query to retrieve this data? I know about connections, etc., but I just don't see how to apply it.

Thanks. Hope this makes sense.

Answer 1

This is a simple inner join between two tables to return the desired rows:

 SELECT P.PRODUCTNAME, O.QUANTITY FROM PRODUCTS P INNER JOIN ORDER O ON P.PRODUCTID = O.PRODID WHERE O.ORDERID = <order id> 

Answer 2

 SELECT PRODUCTS.productName AS productName, `ORDER`.quantity AS quantity FROM `ORDER` INNER JOIN PRODUCTS on `ORDER`.prodID=PRODUCTS.productID WHERE .. 

You also want to rename the ORDER table - using reserved words, because table names are not the best of styles.

Answer 3

Select p.productname, q.quantity from product_table p, quantity_table q, where p.productId = q.productId;