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

General SQL Server Forums

New to SQL Server Programming

Tables problem

Author

Topic

yoni1887
Starting Member

6 Posts

Posted - 2010-08-18 : 21:19:21

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!

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-08-19 : 03:27:50

I dont think this is your requirement, if it is then well and good.


SELECT Name AS Product_name FROM PrductTable PD INNER JOIN PromotionsTable PT ON PD.Merchant_id = PT.Merchant_id
SELECT merchant_Name FROM MerchantTable MD INNER JOIN PromotionsTable PT ON MD.Merchant_id = PT.Merchant_id

If its not then let us know.

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-19 : 13:48:13

Cant understand why you dont have a unique identifier for product table. How do you identify a product then?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

yoni1887
Starting Member

6 Posts

Posted - 2010-08-19 : 13:49:57

That's a good point. I'll suggest that. But in the meantime this is how its set up and thats how i have to access the data...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-19 : 13:51:36

But its not at all a good design i would say. There should be a PK column in Product which identifies uniquely each product

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

yoni1887
Starting Member

6 Posts

Posted - 2010-08-19 : 21:08:29

Hi vaibhavktiwari83 Thanks for your help.
The only thing left I need to figure out is to print the date if we are currently in the date range of the promotions. Any ideas how to do this?
Thanks!

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-08-20 : 02:20:25

quote:
Originally posted by yoni1887

Hi vaibhavktiwari83 Thanks for your help.
The only thing left I need to figure out is to print the date if we are currently in the date range of the promotions. Any ideas how to do this?
Thanks!

If we are currently means you are talking about current date or what?
I am not sure this is your requirement
if yes then


SELECT Name AS Product_name, 
CASE WHEN GETDATE() >= Start_Date AND GETDATE() <= End_date THEN GETDATE() ELSE NULL END Date
FROM PrductTable PD 
INNER JOIN PromotionsTable PT ON PD.Merchant_id = PT.Merchant_id

SELECT merchant_Name,
CASE WHEN GETDATE() >= Start_Date AND GETDATE() <= End_date THEN GETDATE() ELSE NULL END Date 
FROM MerchantTable MD 
INNER JOIN PromotionsTable PT ON MD.Merchant_id = PT.Merchant_id

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

yoni1887
Starting Member

6 Posts

Posted - 2010-08-20 : 02:55:16

Yes, that was what I meant. That was very helpful! Thanks so much, I'm slowly starting to pick up on it!

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-08-20 : 03:03:49

Very nice... Keep it up...

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources