select statement 1

[stroyer74]

I have two tables that I would like to pull information from. TableA has my part information, and TableB has the vendor specific information about that part. A partID can exist only once in the TableA, but partID can exist many times in TableB (once per vendor). In the live data, a particular partID will most often be purchased from only one vendor, but some partID’s may be purchased from 2 or 3 different vendors. I am attempting to list out this information in a query, showing a single row for each TableA.partID:

TableA.partID, TableA.desc, TableA.uom, TableB.vendorID, TableB.quote, TableB.vendorID as vendorID2, TableB.quote as quote2, TableB.vendorID as vendorID3, TableB.quote as quote3

… listing the first three vendors (ordered by TableB.lastPurchasedDate desc) that are found for each TableA.partID. I am struggling to understand how to write a select statement for this (or coming up with the right terms in my Google searches). Would anyone be willing to push me in the right direction?


TableA
partID varchar(50) primary key
desc varchar(50)
uom varchar(2)
lastCost decimal


TableB
rowID int primary key
vendorID varchar(50)
partID varchar(50)
vendorPartID varchar(50)
quote decimal
lastPurchaseDate date


Thanks in advance,
Sheldon

 

[gmmastros]

try this. If it works and you want me to explain, please let me know.

; With VendorParts As
(
  Select Row_Number() Over (Partition By TableB.PartNumber Order By TableB.lastPurchaseDate) As RowId,
         TableB.*
  From   TableB
)
Select  *
From    TableA
        Inner Join VendorParts
          On TableA.partId = VendorParts.partId
          And VendorParts.RowId = 1

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[stroyer74]

Interesting stuff - I've not used this before. I tried your example, and it works for displaying the part info and vendor info of the first vendor. I can update the last line to read

And (VendorParts.RowId = 1 or VendorParts.RowID = 2)

but then that gives me two data rows for each part that has two vendors. Or maybe I'm missing something.

 

[gmmastros]

instead, try changing this:

Row_Number() Over (Partition By TableB.PartNumber Order By TableB.lastPurchaseDate [!]DESC[/!]) As RowId

And change it back to .RowId = 1



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[TamarGranor]

George - seems to me your solution would work if he only wanted the single most recent, but he's asking for the three vendors used most recently for each part. I can see how to adapt what you wrote to end up with one row for each part/vendor combination, but I'm not sure how he gets from there to what he seems to want, which is:

[pre]
PartID VendorID(1) Quote(1) VendorID(2) Quote(2) VendorID(3) Quote(3)
[/pre]

that is, one row for each part. No doubt this complicated by the possibility of not having three vendors for some parts.

Tamar

 

[gmmastros]

Tamar,

After re-reading the original question, it's obvious that I didn't understand it correctly. Thanks for pointing that out.



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

This query will probably work. It's untested.

; With VendorParts As
(
  Select Row_Number() Over (Partition By TableB.PartNumber Order By TableB.lastPurchaseDate) As RowId,
         TableB.*
  From   TableB
)
Select  TableA.*,
        VendorParts1.vendorId As vendorId1,
        VendorParts1.quote    As quote1,
        VendorParts2.vendorId As vendorId2,
        VendorParts2.quote    As quote2,
        VendorParts3.vendorId As vendorId3,
        VendorParts3.quote    As quote3
From    TableA
        Inner Join VendorParts As VendorParts1
          On TableA.partId = VendorParts1.partId
          And VendorParts1.RowId = 1 
        Left Join VendorParts As VendorParts2
          On TableA.partId = VendorParts2.partId
          And VendorParts2.RowId = 2
        Left Join VendorParts As VendorParts3
          On TableA.partId = VendorParts3.partId
          And VendorParts3.RowId = 3

Note:

1. This will only return rows where there is at least one row in TableB with the corresponding partId.

2. If there are more than 3 rows in TableB, only the most recent 3 will be shown.

3. If there are less than 3 rows in TableB for a particular part, the vendorId and quote will be null.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[stroyer74]

That did it! That is what I was looking for - thank you very much. The only part that I'm missing is for new parts that have been created, but do not have any corresponding information in the vendor table yet - they do not show up; but you specified that earlier that they would not show up unless there is at least one record in the TableB table.. But maybe I could do a union with a second query that searches for parts where vendor info is null.

 

[gmmastros]

try changing the inner join to a left join.

From    TableA
        [!]Left[/!] Join VendorParts As VendorParts1
          On TableA.partId = VendorParts1.partId
          And VendorParts1.RowId = 1

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom