Title: SQL: JOIN two tables with distinct rows from one table
Tags: sql;sql-server-2008;tsql;join
Question: 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.


Here is the accepted answer: 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



{'source': 'StackExchangeDataset', 'subset': 'stackoverflow.com', 'metadata': {'AnswerCount': 1, 'CommentCount': 0, 'ContentLicense': 'CC BY-SA 3.0', 'CreationDate': '2013:00:07/10/13', 'FavoriteCount': 2.0, 'PostTypeId': 1, 'Tags': '<sql><sql-server-2008><tsql><join>', 'Title': 'SQL: JOIN two tables with distinct rows from one table', 'ViewCount': 963, 'id': 17568760, 'user_AboutMe': '', 'user_AccountId': 2454573.0, 'user_DisplayName': 'ubaid', 'user_Reputation': 148.0, 'user_Views': 19.0, 'user_id': 2140091.0}}