CODEWITHSUNDEEP
sqlsql-servert-sql
xdtTransform
xdtTransform

Reputation: 2057

Take the first row then the second etc when joinning two table

Context :

This is a simplification. I will use a simple Cart/Product analogy.
Joe puts 3 TunaCan in his cart. 'Table A' has 3 row. Holding information the Product and the Cart.

For now they are random TunaCan. We wont know their serial before he reachs the grocery checkout.

At the time of payement, we need to associate, each row from the Product to one of the serial we scanned 'Table B'.
A serial - cart product is an unique couple, two product can't have the same serial.
If there is less serial than product the remaning product will have a default serial 'DefaultValue'

Table A : CartProduct(string Product, int CartID)

TunaCan 1   
TunaCan 1
TunaCan 1

Table B : ScannedProduct(string Serial, Product)

Foo         TunaCan
Bar         TunaCan
FooBar      TunaCan

The expect result could be : 

TunaCan     1   Foo 
TunaCan     1   Bar
TunaCan     1   FooBar

"Could" because there is 7 possible combinaison for a set of 3 elements.

In LinQ I will use the index to access the row in the second collect 

var result = TableA.Select((x,i)=> new{ x.propertie, TableB[i]})

Upvotes: 0

Views: 119

Answers (2)

Zhorov
Zhorov

Reputation: 29993

If I understand your question correctly, you may try to number your CartProduct and ScannedProduct tables, and then join them on this number:

-- Tables
CREATE TABLE #CartProduct (
    Product varchar(100), 
    CartID int
)
INSERT INTO #CartProduct 
    (Product, CartID)
VALUES 
    ('TunaCan', 1),   
    ('TunaCan', 1),
    ('TunaCan', 1)

CREATE TABLE #ScannedProduct(
    Serial varchar(10),
    Product varchar(100)
)
INSERT INTO #ScannedProduct 
    (Serial, Product)
VALUES 
    ('Foo', 'TunaCan'),
    ('Bar', 'TunaCan'),
    ('FooBar', 'TunaCan')

-- Statement 
;WITH cte1 AS (
    SELECT 
        Product, 
        CartID,
        ROW_NUMBER() OVER (PARTITION BY Product ORDER BY Product) Rn
    FROM #CartProduct
), cte2 AS (
    SELECT 
        Product, 
        Serial,
        ROW_NUMBER() OVER (PARTITION BY Product ORDER BY Product) Rn
    FROM #ScannedProduct
)
SELECT
    cte1.Product Product,
    cte1.CartID CartID,
    ISNULL(cte2.Serial, 'Default value') Serial
FROM cte1
LEFT JOIN cte2 ON (cte1.Product = cte2.Product)  AND (cte1.Rn = cte2.Rn)

Output:

Product CartID  Serial
TunaCan 1       Bar
TunaCan 1       Foo
TunaCan 1       FooBar

Upvotes: 2

maddy
maddy

Reputation: 50

You need to do cross join: select a.stringproduct,a.cartID ,b.product from A a cross join B b

Upvotes: -1

Related Questions