SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Query help on Temporal DB
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

SergioM
Posting Yak Master

163 Posts

Posted - 08/19/2014 :  12:00:42  Show Profile  Reply with Quote
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

Edited by - SergioM on 08/19/2014 12:01:48

tkizer
Almighty SQL Goddess

USA
37123 Posts

Posted - 08/19/2014 :  12:44:46  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

163 Posts

Posted - 08/19/2014 :  12:54:55  Show Profile  Reply with Quote
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

USA
37123 Posts

Posted - 08/19/2014 :  12:59:35  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

163 Posts

Posted - 08/19/2014 :  15:14:56  Show Profile  Reply with Quote
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

163 Posts

Posted - 08/19/2014 :  15:33:48  Show Profile  Reply with Quote
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

163 Posts

Posted - 08/20/2014 :  10:00:53  Show Profile  Reply with Quote
Can anyone help me?

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

bitsmed
Constraint Violating Yak Guru

405 Posts

Posted - 08/20/2014 :  19:09:38  Show Profile  Reply with Quote
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

USA
37123 Posts

Posted - 08/20/2014 :  19:20:17  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000