How to lookup from the second source datatable to the first datatable with vb.net

How to lookup from the second source datatable to the first datatable with vb.net.

Is it possible to apply lookup datatable if possible? please guide me

Thanks

 Private table1 As New DataTable
    Private table2 As New DataTable
    Protected Overrides Sub OnLoad(e As EventArgs)
        MyBase.OnLoad(e)
        '_myTable = New DataTable
        table1.Columns.AddRange({
        New DataColumn("Column1", GetType(String)),
        New DataColumn("Column2", GetType(String)),
        New DataColumn("Column3", GetType(String)),
        New DataColumn("Size", GetType(String)),
        New DataColumn("Qty", GetType(Integer))
    })
        table1.Rows.Add("TEST 3000", "TEST", "BHTF10", "5L", 25)
        table1.Rows.Add("TEST 2000", "TEST", "BHTF15", "S", 55)
        table1.Rows.Add("TEST 3000", "TEST", "BHTF10", "M", 65)
        table1.Rows.Add("TEST 1000", "TEST", "BHTF25", "6L", 10)
        table1.Rows.Add("TEST 3000", "TEST", "BHTF10", "S", 12)
        DataGridView1.DataSource = table1
    End Sub
    Private Sub createtabletwo()

        table2.Columns.AddRange({
        New DataColumn("Size", GetType(String)),
        New DataColumn("SizePriority", GetType(Integer))
    })
        table2.Rows.Add("5L", 13)
        table2.Rows.Add("S", 2)
        table2.Rows.Add("M", 3)
        table2.Rows.Add("6L", 15)
        table2.Rows.Add("3L", 9)

    End Sub

Desired Result

Column1	Column2	Column3	Size	Qty	SizePriority
TEST 3000	TEST	BHTF10	5L	25	13
TEST 2000	TEST	BHTF15	S	55	2
TEST 3000	TEST	BHTF10	M	65	3
TEST 1000	TEST	BHTF25	6L	10	15
TEST 3000	TEST	BHTF10	S	12	2

Solution

If looking to do using just a simple Select Case decision tree, nothing too complicated. In fact I would almost go as far as to say looking over your previous questions you've posted and answers you've, you already know how to do this, you need to actually apply the logic and learnings others are giving you.

    Dim table1 As New DataTable("Players")
    table1.Columns.Add(New DataColumn("ProductCode", GetType(String)))
    table1.Columns.Add(New DataColumn("Remark", GetType(String)))
    table1.Columns.Add(New DataColumn("ColorCode", GetType(String)))
    table1.Columns.Add(New DataColumn("Size", GetType(String)))
    table1.Columns.Add(New DataColumn("Qty", GetType(Integer)))
    table1.Columns.Add(New DataColumn("SizePriority", GetType(Integer)))
    table1.Rows.Add("TEST 3000", "TEST", "BHTF10", "5L", 25)
    table1.Rows.Add("TEST 2000", "TEST", "BHTF15", "S", 55)
    table1.Rows.Add("TEST 3000", "TEST", "BHTF10", "M", 65)
    table1.Rows.Add("TEST 1000", "TEST", "BHTF25", "6L", 10)
    table1.Rows.Add("TEST 3000", "TEST", "BHTF10", "S", 12)

    For Each row As DataRow In table1.Rows

        Select Case row("Size").ToString()
            Case "XS"
                row("SizePriority") = 1
            Case "S"
                row("SizePriority") = 2
            Case "M"
                row("SizePriority") = 3
            Case "L"
                row("SizePriority") = 4
            Case "XL"
                row("SizePriority") = 5
            Case "XXL"
                row("SizePriority") = 6
            Case "2L"
                row("SizePriority") = 7
            Case "3L"
                row("SizePriority") = 8
            Case "4L"
                row("SizePriority") = 9
            Case "5L"
                row("SizePriority") = 10
            Case Else
                row("SizePriority") = 11
        End Select
        row.AcceptChanges()
    Next


    Dim table1View As New DataView(table1)
    table1View.Sort = "ProductCode Desc, ColorCode, SizePriority"
    For Each row As DataRowView In table1View
        Console.WriteLine($"{row(0)}, {row(1)}, {row(2)}, {row(3)}, {row(4)}, {row(5)}")
    Next

Nothing any more complicated than that. Of course, you do this using lamda/linq expressions, but this is a verbose way to do it. Alternative since the SizePriority table is likely to be the smaller (I'm guessing) in real life, it may be better performance to switch this around. Iterate the Size Priority table, updating Table1 using a Where clause. I'm a bit fuzzy on the exact syntax with that now days, so going to leave it there, up to you if you want to research that.

If you're looking to do it as a query from your DB, it's just a simple join. Nothing more complicated than that, you might even consider using the Order By clause on that query which will make this and previous question pretty much redundant.

Select
    <order>.Code,
    <order>.Name,
    <order>.ColorCode,
    <order>.Size,
    <order>.Quantity,
    <SizePriority>.Priority
From <Order>
    Inner Join <SizePriority> ON <SizePriority>.Size = <Order>.Size
Order By Code Desc, ColorCode, Priority

Note here, I don't know your DB schema so I've used <order> and <SizePriority> as placeholders, you will need to replace them with your actual table names whatever they are. You may also need some where condition on this to filter results.