HELP Needed to come up with a DB Search.

[Mr B]

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 if you need more info. 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 (ORDERS) contains information on orders that are placed. Includes info like name, address,
etc and has an OrderID field.

Table 2 (ITEMS) 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 (OPTIONS) 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.

This all keys off the specific Item I'm looking for...

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.

~~~~~~~~~~~~~~~~~~~~~~~~`

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
[email]--XkelemvorX@XexecpcX.Xcom[/email]X (remove the X's)

[McKirahan]

"Mr B" <ladeeda@hahaha.com> wrote in message
news:ae4000t3mevs72ltkb0huoh4iddtskhgog@4ax.com...

> 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 if you need more info. 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 (ORDERS) contains information on orders that are placed. Includes

info like name, address,

> etc and has an OrderID field.
>
> Table 2 (ITEMS) 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 (OPTIONS) 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.
>
> This all keys off the specific Item I'm looking for...
>
> 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.
>
> ~~~~~~~~~~~~~~~~~~~~~~~~`
>
> 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
> [email]--XkelemvorX@XexecpcX.Xcom[/email]X (remove the X's)

Perhaps this will help; watch for word-wrap:

SELECT
ORDERS.OrderID,
ORDERS.Name,
ITEMS.ItemDescription,
ITEMS.RowID,
OPTIONS.OptionDescription
FROM (ORDERS
INNER JOIN ITEMS
ON ITEMS.OrderID = ORDERS.OrderID)
INNER JOIN OPTIONS
ON OPTIONS.RowID = ITEMS.RowID
WHERE ORDERS.OrderID = 1
ORDER BY
ORDERS.Name,
ITEMS.ItemDescription,
OPTIONS.OptionDescription;

This SQL statement will return one row for each OPTIONS row that exists for
each ITEMS row as well as one row for each ITEMS row that exists for each
ORDERS row.

Just loop through the resulting recordset and break on each OPTIONS within
ITEMS within ORDERS.

Remove the "WHERE ORDERS.OrderID = 1 " line for all ORDERS.

Of course, your field names may differ.