Complex query, don't know where to start

[UltraSmooth]

I have a promotion table that contains items that are on sale for a certain promotion. A promotion item can be setup based on the following criteria:

Item#, Dept, Class, Supplier, Product_Line,Category_1,Category_2,Category_3.

A promotion item can be entered by filling in any of the criteria above. For instance, items can be included in a promotion by entering just the Item#, or can be included by just specifying the Dept,Supplier,Product_Line,Dept & Class, etc. and all items that match on those details will be included in the promotion.

I need to make a join by Item# to a sales table to generate a sales summary for a promotion.

In order to first get a list of all item#'s that are in a promotion I need to do a look up in the item_detail table to determine the item# for items that are included but were not associated directly by item# but by their details such as Dept,Class, Supplier...

The problem is the number of variations (since an item can be assoiated by dept,dept & class,supplier,vendor ....)

How would I go about writing a query to generate this complete item# list? I really don't know how to tackle this properly. I hope my description is clear enough.

Thank you for any help.

 

[audiopro]

I think you are trying to find a complicated solution to a simple problem. We use an additional field in the product table which we use for special offers etc. This column is populated with a number of offer codes which can be queried very easily.
This may not suit your needs but there is always another way.

Keith

http://www.studiosoft.co.uk

 

[UltraSmooth]

I can't make any changes to the database. The database is part of our Point of sale software. I am just reporting off it.

 

[audiopro]

How do you tell promotion items and ordinary sale items apart?

Keith

http://www.studiosoft.co.uk

 

[UltraSmooth]

There is an inventory table which contains all information about an item. When an item is included in a promotion a record is created in the promotions table for that item with a promotion id. Examples of some promotion table entrie are:

Item#___Dept__Class____Supplier___Prod_Line___Promo_ID
34532_________________________________________650034
________H5____________________________________650034
________H7____123_____________________________650034
_______________________ABC____________________650034

I need to join these records agains the inventory table to get a full list of items and their details and then against the sales table to produce the summary.

 

[audiopro]

You have certainly created a challenge for yourself.
I usually work in Foxpro which has this kind of thing for breakfast but even that would be a hard.

It looks like you have inherited a poorly designed database but that doesn't help you get your job done.

OK - so you can't edit the main table but appear to have some control over the promotions table.
I would be trying to get your promo table fully populated in order to make your query possible. As it stands at the moment, how would you cross reference a specific item from the supplier ID alone?

Keith

http://www.studiosoft.co.uk

 

[UltraSmooth]

Unfortuantely I can't make any modifications to any of the tables, I have to stick with what I have.

As for you item/supplier question. Say we have have all items by supplier ABC on sale. In the promotions table there will be one record with the Supplier field containing the suppliers code, as well as the promotion id as I showed above. To determine which items are included I need to make a join with the inventory table on that field, so something like

SELECT *
FROM inventory INNER JOIN promotions
ON inventory.supplier=promotions.supplier

For each permutation I have to do something similar. So for a case where all items for a Dept/Class are on sale (in the example above H7/123)

SELECT *
FROM inventory INNER JOIN promotions
ON inventory.dept=promotions.dept AND inventory.class=promotions.class

Would it make sense to create a subquery for each permuation and then UNION them all together and do a final DISTINCT SELECT on the fields I need from this derived table?

 

[audiopro]

I am afraid this just gets more confusing.
You are trying to obtain a sales summary, presumably to determine how much profit has been mad from a particular promotion.
Supplier ABC supplies:-
1. - Squiddy Widgets @ £13.67 each
2. - Bobble Tags @ £17.45 each
3. - Cobra Belts @ £3.56 each

You have sold 50 of item 1, 20 of item 2 and 10 of item 3.
From your supplier code, you can only identify you have sold 80 items but have no way of knowing what they were.
You will need more than just a simple query to get the information you require.

Keith

http://www.studiosoft.co.uk

 

[UltraSmooth]

The point of first joining with the inventory table is to generate a list of items for the Supplier. A simple example of an inventory table:

Item# Dept Class Supplier Category Cost ...
9833__H5___123___ABC______Home_____5.99
3994__H7___543___ABC______Patio____3.99
3423__D5___234___DEF______Auto_____7.99

So joining with the promotion table on the supplier field I get a list of items that belong to the supplier 'ABC':

9833__H5___123___ABC______Home_____5.99
3994__H7___543___ABC______Patio____3.99

The sales table has a weekly/monthly/yearly record for each item where it keeps track of units sold, retail amount, etc ... So joining those records against the sales table will give me the sales summary for each item.

My problem is generating my item list to use in the sales table join because of the way promotions are stored.