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
 Query help on Temporal DB

Author  Topic 

SergioM
Posting Yak Master

170 Posts

Posted - 2014-08-19 : 12:00:42
This is my first attempt at a temporal DB & also my first attempt with a client-facing database. Probably a stupid combination, but here I am.

I have a db with 6 tables
* BuyerFeedback
* ComputerName
* IPAddress
* ProductData
* SellerData
* VendorData


For any given Seller (SellerData) there are many products (ProductData). For any given Product there are instances of that product each time it was collected. It's (poorly) labeled VendorData. Lastly the BuyerFeedback table stores the criteria provided by the buyer. So the n amount of feedback received or y amount of reviewers. This will change each time it is polled.

I want to create a query that selects all product data (ProductData) as well as the latest instance of VendorData, & the latest instance of BuyerFeedback & group that together with BuyerFeedback. This is what I have so far. It's pretty poor because I don't know how to get the top result per row. I either get the top results of all of the rows or all of the rows... Any help is appreciated! Thanks


SELECT     vd.ID, vd.ProductID, vd.IsPrime, vd.Price, vd.SellerID, vd.DateCollected AS 'vd_DateCollected', vd.ComputerName, vd.IPID, sd.SellerName, pd.ASIN, 
pd.ComputerName AS pd_ComputerName, pd.Country, pd.DateCollected AS 'pd_DateCollected', pd.ImageLink, pd.MSRP, pd.Title, ip.IP, ip.DateCollected AS 'ip_DateCollected'
,bf.Reviewers, bf.Stars

FROM dbo.VendorData AS vd INNER JOIN
(SELECT ID, AmazonSellerID, SellerName, DateCollected
FROM dbo.SellerData) AS sd ON vd.SellerID = sd.ID INNER JOIN
(SELECT ID, ASIN, Title, MSRP, ImageLink, DateCollected, ComputerName, IPID, Country
FROM dbo.ProductData) AS pd ON pd.ID = vd.ProductID INNER JOIN
(SELECT ID, IP, DateCollected
FROM dbo.IPAddress) AS ip ON ip.ID = vd.IPID INNER JOIN
(SELECT [ID] ,[ProductID] ,[Stars] ,[Reviewers] ,[DateCollected] ,[ComputerName] ,[IPID]
FROM dbo.BuyerFeedback) AS bf ON bf.ProductID = pd.ID



SQL Version - 2.56 MB
https://www.dropbox.com/s/yjhkbz1pdo9664v/AmazonData.sql
ZIP Version - 163 KB
https://www.dropbox.com/s/couum1j2bnxwwpa/AmazonData.zip


-Sergio
I use Microsoft SQL 2008

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-08-19 : 12:44:46
Please post your question like this: http://www.sqlservercentral.com/articles/Best+Practices/61537/

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

SergioM
Posting Yak Master

170 Posts

Posted - 2014-08-19 : 12:54:55
quote:
Originally posted by tkizer

Please post your question like this: http://www.sqlservercentral.com/articles/Best+Practices/61537/

I'm not sure what I did which was different from the etiquette. It has the abstract, my own query & all of the table structures & data in a runnable SQL statement. Which part is not to your liking?

-Sergio
I use Microsoft SQL 2008
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-08-19 : 12:59:35
I didn't click on your links to check (and still haven't). You might want to post the create table/inserts/expected output here in this post rather than on dropbox as some, like me, won't click on the links to download it. Not being lazy, just being security-cautious.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

SergioM
Posting Yak Master

170 Posts

Posted - 2014-08-19 : 15:14:56
quote:
Originally posted by tkizer

I didn't click on your links to check (and still haven't). You might want to post the create table/inserts/expected output here in this post rather than on dropbox as some, like me, won't click on the links to download it. Not being lazy, just being security-cautious.


I hear you. It's just that it's 5K rows. I would happily paste it into the forum, but I thought it would not look good. Would Pastebin be better? Or should I just post it in here as is?

Separately, I don't know if you're familiar with Dropbox, but it doesn't automatically download anything. It shows you a preview if possible.


-Sergio
I use Microsoft SQL 2008
Go to Top of Page

SergioM
Posting Yak Master

170 Posts

Posted - 2014-08-19 : 15:33:48
Update: It's too large for pastebin & the forum gives me the following error when I paste it here:

Request object error 'ASP 0104 : 80004005'

Operation not Allowed

/FORUMS/inc_header.asp, line 185

-Sergio
I use Microsoft SQL 2008
Go to Top of Page

SergioM
Posting Yak Master

170 Posts

Posted - 2014-08-20 : 10:00:53
Can anyone help me?

-Sergio
I use Microsoft SQL 2008
Go to Top of Page

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2014-08-20 : 19:09:38
Maybe something like this:
select *
from (select vd.ID
,vd.ProductID
,vd.IsPrime
,vd.Price
,vd.SellerID
,vd.DateCollected as 'vd_DateCollected'
,vd.ComputerName
,vd.IPID
,sd.SellerName
,pd.ASIN
,pd.ComputerName as pd_ComputerName
,pd.Country
,pd.DateCollected as 'pd_DateCollected'
,pd.ImageLink
,pd.MSRP
,pd.Title
,ip.IP
,ip.DateCollected as 'ip_DateCollected'
,bf.Reviewers
,bf.Stars
,row_number() over (partition by vd.ProductID order by bf.DateCollected desc) as vd_rn
,row_number() over (partition by bf.ProductID order by bf.DateCollected desc) as bf_rn
from dbo.ProductData as pd
inner join dbo.VendorData as vd
on vd.ProductID=pd.ID
inner join dbo.BuyerFeedback as bf
on bf.ProductID=pd.ID
inner join dbo.SellerData as sd
on sd.ID=vd.SellerID
inner join dbo.IPAddress as ip
on ip.ID=vd.IPID
) as a
where a.vd_rn=1
and a.bf_rn=1
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-08-20 : 19:20:17
quote:
Originally posted by SergioM

quote:
Originally posted by tkizer

I didn't click on your links to check (and still haven't). You might want to post the create table/inserts/expected output here in this post rather than on dropbox as some, like me, won't click on the links to download it. Not being lazy, just being security-cautious.


It's just that it's 5K rows.



We only need a few rows of each table and expected output using those rows. When I post a question, I typically give 5-10 rows per table and ensure there is linking data for the joins and that the expected output matches the data in the tables.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page
   

- Advertisement -