Inventory Tables problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Inventory Tables problem

Author

Topic

yoni1887
Starting Member

6 Posts

Posted - 2010-08-18 : 21:20:15

Hi all I'm a bit new to SQL and have this problem at my work.
Using the following database schema:
PromotionsTable
merchant_id
sku
start_date
end_date

ProductTable
merchant_id
sku
name
price

MerchantTable
merchant_id
merchant_name

I need to construct a SQL query to display all products that are currently having a promotion.
NOTE: sku is not unique to a product, but it is unique to a merchant

Then, I also need to construct a SQL query to display all merchants that are currently having a promotion.

Any help would be greatly appreciated.
Thanks!

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-08-19 : 00:05:56

I feel its an assignment. Can you just show us what you have tried.
We will be happy to help you after seeing some effort from your side.

yoni1887
Starting Member

6 Posts

Posted - 2010-08-19 : 01:21:11

Sure, I have this for the first one:
SELECT * FROM ProductTable, WHERE (ProductTable.merchant_id = PromotionsTable.merchant_id AND ProductTable.sku = PromotionsTable.sku)
AND (PromotionsTable.start_date <= CONVERT(VARCHAR(10), GETDATE(), 120) < PromotinsTable.end_date)

and this for the 2nd:

SELECT * FROM MerchantTable, WHERE MerchantTable.merchant_id = PromotionsTable.merchant_id AND
(PromotionsTable.start_date <= CONVERT(VARCHAR(10), GETDATE(), 120) < PromotionsTable.end_date)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources