How do I select quantities for each day, for each item?

Question

I've got a database and its designed roughly like this:

tblParts
----------
partID

tblOrders
----------
partID
date
quantity

Ultimately I'd like to product output that looks something like this:

partID   Aug 10   Aug 11   Aug 12 ... (say .. 100 days)
M061101  5        25       15
M061102  10       0        20

I can write a query to grab all the info for a single part:

SELECT * FROM tblParts LEFT JOIN tblOrders USING (partID);

but the results span many rows. So for many parts ... lots and lots of rows. How can I construct a query to return one row per part?

The ideal situation is being able to chose a range of dates and ending up with one row per part for that range of dates.

You would be much better off making a pivot table in Excel. Also I don't see why you would bother joining the tblparts table. The partid column is already on the tblorders table. Unless tblparts also has a description field that you want to bring back, there is no point. In either case you could use an inner join, there's no need for an outer join given that I assume orders can only be placed for parts that exist. — Brian DeMilia, Aug 25 '14 at 20:51
@brian-demilia because in posting a question its easier for those reading to simplify as much as possible. In what real-world database would a table have a single column for inventory number and nothing else? — just.another.newbie, Aug 25 '14 at 21:45

0 Answers0