Multiple tables, multiple queries, need unified output

[eskybel]

I will try to explain this the cleanest way I can.



I have a number of tables, and a number of queries in my actual project, but I will break out the important ones:

I have a PriceDiscount table that shows itemIDs, and their agreed prices, based on date ranges. So, when the date is between the date ranges, the current price is what's listed. The odd date range is where they are listed as 1/1/00, which is actually a blank field in the database, signifying there is no end date. When queried, it autofills 1/1/1900 date.

EX:
Item fromdate todate price
123 9/1/13 1/1/00 $6.00
123 8/1/13 8/31/13 $7.00
321 8/1/13 8/31/13 $1.00
321 10/1/13 10/31/13 $1.50
111 9/1/13 9/30/13 $.50

I have another table, InventItem that holds itemIDs, their descriptions, and their standard price.

Item Name Price
123 Burger $8.00
321 Soda $2.00
111 Fries $1.00

You'll see in the examples, Burgers are on standard priced at $8.00, and have been on sale through August, and are currently on sale at a different price from Sept, until forever. Soda was on sale in August, and is on sale in October, but will end at 10/31/13. Fries were on sale in September, but are no longer on sale.

What I need to do is be able to gather the current discounted price of all items on sale, and gather all items that are not on sale, and display the current pricing. So, in this example, I would want my output to show that Burgers are $6.00, and sodas are $1.50, and fries are $1.00

What I have is a query that goes out and grabs everything that has a todate of 1/1/1900, or is greater than today. It grabs those and shows me the items that have current sale pricing. Great, that's one chunk. I have another query that grabs every item, and their standard price. Great, that's another chunk. But now, when I combine them, I get duplication on my output. I'll see that Burgers are $8.00 (standard) and $6.00 (sale), soda is $2.00 (standard) and $1.50 (sale), and Fries are $1.00 (standard). I just want to see the current pricing for all items sale or standard, but not both.

I imagine somewhere I will need code or something that says "if on sale, give me sale cost. If not, give me standard cost." Can I do that in one query?

Sorry if that's confusing. Thanks in advance.

[Dal Jeanis]

You don't need multiple queries, you just need a LEFT JOIN to the Discount table, and if there's no appropriate discount, you use the current price. This will work unless you accidentally have two valid discounts on an item at the same time, in which case you'll (obviously) get two different records back for the same item.

Try This:

SELECT
  TI.Item,
  TI.Name,
  NZ(TD.Price,TI.Price)
FROM InventItem AS TI LEFT JOIN PriceDiscount AS TD ON TI.Item = TD.Item
WHERE (( TD.FromDate <= DATE) AND ((TD.ToDate >= DATE) OR (TD.ToDate is Null)))

Generally, I would prefer to have my ToDate be a long-future real date (such as 12/31/2090), to avoid having to test for NULL.

[eskybel]

You don't need multiple queries, you just need a LEFT JOIN to the Discount table, and if there's no appropriate discount, you use the current price. This will work unless you accidentally have two valid discounts on an item at the same time, in which case you'll (obviously) get two different records back for the same item.

Try This:

SELECT
  TI.Item,
  TI.Name,
  NZ(TD.Price,TI.Price)
FROM InventItem AS TI LEFT JOIN PriceDiscount AS TD ON TI.Item = TD.Item
WHERE (( TD.FromDate <= DATE) AND ((TD.ToDate >= DATE) OR (TD.ToDate is Null)))

Generally, I would prefer to have my ToDate be a long-future real date (such as 12/31/2090), to avoid having to test for NULL.

Ok, I made a mistake. I have the InventItem table that holds the itemID, the itemname, and then I have a InventItemModule table that holds the standard price, then a PriceDiscount table that holds the discounts. I tried to loop that table into the mix and got an "ambiguous outer join" error. I guess since I have a lot more tables and a couple queries along with this simplified example of what I have, I assume I will continue to hit these problems.

But even so, I simplified it and adjusted the tables/fields names to fit my situation in the code above, and the results I'm getting are much larger than I should. I changed "is null" to #1/1/1900# because that's how the database looks. It returned a 126k records, when I should only have somewhere in the ballpark of 10-20k.

[Dal Jeanis]

Which means that your JOIN is incorrect. You'll have to post the actual structure of your queries and tables before we can determine what precisely is wrong.