locked
Get Result From Two Table RRS feed

  • Question

  • User-807418713 posted

    Hello This is my Table 1 Data

    Table 1
    Field1 Field2 Qty
    B1 UT 10
    JQ1 JJJJ 20
    M1 59 40

    Table 2 Data

    Table2
    Field1 Field2
    1 AA
    2 KK
    3 ZZ

    1) I want output like this

    Field1 Qty
    B1 - AA 10
    B1 - KK 10
    B1 - ZZ 10
    JQ1 - AA 20
    JQ1 - KK 20
    JQ2 - ZZ 20
    M1 - AA 40
    M1 - KK 40
    M1 - ZZ 40

    2) If I Pass M1 Then I Want Resulet Set Like This

    Field1 Qty
    M1 - AA 40
    M1 - KK 40
    M1 - ZZ 40

    Thanking you

    Wednesday, April 14, 2021 7:13 AM

Answers

  • User452040443 posted

    Try:

    select
        t1.Field1 + ' - ' + t2.Field2 as Field1,
        t1.Qty
    from Table1 as t1
    cross join Table2 as t2
    -- where t1.Field1 = 'M1'

    Hope this help

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, April 14, 2021 5:46 PM

All replies

  • User452040443 posted

    Hi,

    Try:

    select
        concat(t1.Field1, ' - ', t2.Field2) as Field1,
        t1.Qty
    from Table1 as t1
    cross join Table2 as t2
    -- where t1.Field1 = 'M1'

    Hope this help

    Wednesday, April 14, 2021 12:31 PM
  • User-807418713 posted

    Hello

    Im using older verison im getting this error

    Msg 195, Level 15, State 10, Line 2
    'concat' is not a recognized built-in function name.

    Wednesday, April 14, 2021 4:05 PM
  • User452040443 posted

    Try:

    select
        t1.Field1 + ' - ' + t2.Field2 as Field1,
        t1.Qty
    from Table1 as t1
    cross join Table2 as t2
    -- where t1.Field1 = 'M1'

    Hope this help

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, April 14, 2021 5:46 PM