Custom code needed, any volunteers

[kelemvor]

Hi all,

I'll try to keep this brief so if you are interested, shoot me an email and I'll get out the specifics. I don't know ASP but I know that I pretty much have to use it to accomplish what I want since ths store is an ASP store.

I have a database that is for an online store. The DB has 3 tables that I am dealing with.

Table 1 contains information on orders that are placed. Includes info like name, address, etc and has an OrderID field.

Table 2 contains a list of items ordered. It references the OrderID so you can tell what items go with what orders. Primary field is the RowID field.

Table 3 has a list of options specified for certain items. Each option is it's own entry and each item can have multiple options. This table references the RowID field so you know what options go with what item and on what order.
E.g. If item 123 is ordered and has 4 options the customer chose, item 123 will appear 4 times in Table 3, one for each option.

PROBLEM
Here's what I'm trying to end up with.

I need to be able to have all the information for a particular item ordered on one row in a table/spreadsheet/whatever.

So if John Smith ordered Item 123 and specified Large, Blue, Square, and Heavy as his options, I'd want a row that would basically show:

John, Smith, address, Item 123, large, blue, square, heavy.

Getting the name and address info isn't the main problem because it's just pulling a row, but getting the 4 rows from Table 3 and putting them in 1 row with the other data, I don't know how to do.
~~~~~~~~~~~~~~~~~~~~~~~~`

Hopefully that made sense. I don't think this would be hard to do if you actually know ASP so if anyonew ould be willing to work with me on this, I can provide the specifics about the database, table names, etc to try to make something work.

Please let me know if you are interested or have other question. Feel free to reply here or email me directly.

Thanks!

--Mike
--kelemvor@execpc.com

[shmoove]

A query like:

select o.name,o.address,o.etc,i.item,p.option from orders o, items i, options p
where i.orderID = o.orderID and p.itemID = i.rowID and o.orderID = X

should do the trick (where X is the order ID you're interested in).

shmoove

[kelemvor]

Will that automatically find the multiple rows in the Options table?

Also I need to key this off the Item ID from the Itemstable.

So basically

-Find every entry in the Items table that has Item ID XXX and record the Order ID and Row ID
-Get the corresponding customer info from the Orders table via the Order ID
-Get the corresponding 4 Option records from the Options table finding all entries with the Row ID from step 1.

[M@rco]

Kelemvor, the fact that you're asking these kinds of questions about *basic* SQL concepts & queries suggests that you would have a lot to gain from investing some time in learning SQL yourself rather than getting others to do it for you.

[whammy]

http://hotwired.lycos.com/webmonkey/02/35/index4a.html?tw=backend

Good tutorial... I think shmoove's code should work, or something close to it - it all depends upon how you're storing the information as well.