Get data from multiple tables

Question

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.

Design seems a bit odd to me I would expect another table: orderediTems, so that way you are not repeating order information (date of order order by ship to etc) for each productID on the order. Or does order have an "Order_header" or something? — xQbert
– xQbert, Commented Jan 4, 2012 at 11:33
@xQbert I do have a ORDRERITEMS table and an ORDERS table, yes. Sorry, I should have made that clearer. — samiles
– samiles, Commented Jan 5, 2012 at 11:34
possible duplicate of Simple SQL Select from 2 Tables (What is a Join?) — Martin Gordon
– Martin Gordon, Commented Jun 17, 2012 at 18:29

StevieG · Accepted Answer · 2012-01-04 11:31:02Z

3

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>

answered Jan 4, 2012 at 11:31

StevieG

8,7391 gold badge26 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Eugen Rieck · Accepted Answer · 2012-01-04 11:32:46Z

3

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.

answered Jan 4, 2012 at 11:32

Eugen Rieck

65.6k10 gold badges75 silver badges97 bronze badges

3 Comments

user359040 Over a year ago

+1 for noting that the ORDER table will need to be quoted, although only MySQL (as far as I know) uses backquotes.

Eugen Rieck Over a year ago

Truly sorry, having SQL and MySQL being synonymous for too long I suspect.

user359040 Over a year ago

I notice from the OP's profile that his other tags include Apache, PHP and Python - chances are he is using MySQL.

Amit · Accepted Answer · 2012-01-04 11:31:51Z

0

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

answered Jan 4, 2012 at 11:31

Amit

13.5k18 gold badges84 silver badges150 bronze badges

Collectives™ on Stack Overflow

Get data from multiple tables

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related