CODEWITHSUNDEEP
sqlsql-server-2008t-sqljoin
ubaid
ubaid

Reputation: 148

SQL: JOIN two tables with distinct rows from one table

This might be a very simple problem but I can't seem to get my head around this since last night.

I have 3 tables

VirtualLicense
VirtualLicenseId    ProductName
-----------------------------------
1           Transaction
2           Query
3           Transaction


Product
ProductId   Name
---------------------------
1           Transaction
2           Query


License
LicenseId   ExpiryDate  ProductId
-----------------------------------------
1           14/07/2013  1
2           13/07/2013  1
3           13/07/2013  2
4           14/07/2013  2

The VirtualLicense and License are joined using ProductName and ProductId mapping using the Product table.

I want to get combination of VirtualLicenseId and LicenseId, where I can basically assign the VirtualLicenseId to a LicenseId. Once a licenseid is assigned to a VirtualLicenseId, it should not be available for the following VirtualLicenseIds. Also, I want that the licenseid for which the expirydate is nearer(smaller) should be assigned first.

So, the result for my example data set should be

VirtualLicenseId    LicenseId
---------------------------------
1                   2
2                   3
3                   1

I do not want to loop over any of the tables for this. I hope my problem is clear from my description and data.

Upvotes: 2

Views: 966

Answers (1)

Nenad Zivkovic
Nenad Zivkovic

Reputation: 18559

You can do something like this:

  • In first CTE - assign rankings for VirtualLicenses within the Product groups.
  • In second CTE - assign rankings for Licensce within the Product groups (order by exp. date)
  • And at the end just join the two subqueries on productID and ranking.
WITH CTE_VL AS 
(
    SELECT *, ROW_NUMBER() OVER (PARTITION BY ProductId ORDER BY vl.VirtualLicenseId ASC) RN
    FROM dbo.VirtualLicense vl
    LEFT JOIN dbo.Product p ON vl.ProductName = p.Name
)
,CTE_License AS
(
    SELECT *, ROW_NUMBER() OVER (PARTITION BY ProductId ORDER BY ExpiryDate ASC) RN
    FROM dbo.License 
)
SELECT VirtualLicenseId, LicenseId 
FROM CTE_VL vl
LEFT JOIN CTE_License l ON vl.ProductId = l.ProductID AND vl.RN = l.RN

SQLFiddle DEMO

Upvotes: 2

Related Questions