CODEWITHSUNDEEP
sql
samiles
samiles

Reputation: 3900

Get data from multiple tables

I need to get data from multiple tables and put it into a subform.

The SubForm columns are 'product name' and 'quantity' and they need to list out the products relating to the order ID.

The tables in question are:

PRODUCTS(productID,productName)

ORDER(orderID,prodID,quantity)

Where the prodID in ORDER refers to the PRODUCTS table.

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

Products(productName)

Order(quantity)

In relation to the orderID.

How can I use a SQL query to get this data? I am aware of joins and so on, but I just can't see how to apply it.

Thank you. I hope this makes sense.

Upvotes: 0

Views: 1893

Answers (3)

Eugen Rieck
Eugen Rieck

Reputation: 65294

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

You migh also want to rename the table ORDER - using reserved words as table names is not the best of styles.

Upvotes: 3

Amit
Amit

Reputation: 13384

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

Upvotes: 0

StevieG
StevieG

Reputation: 8709

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

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

Upvotes: 3

Related Questions