You must Sign In to post a response.
  • Category: SQL Server

    How to apply join on multiple fields

    Hello friends,


    I have table A haveing following column names.

    mcc mnc brand circle
    404 01 Airtel UP
    404 02 Vodafone MP
    404 03 Idea AP
    404 04 Idea Hariyana


    I have another table B which also have

    mcc1 mnc1 mcc2 mnc2
    404 01 404 02
    404 03 404 04


    I need an sql query with join which construct following output

    Brand1 circle1 brand2 circle2
    Airtel UP Vodafone MP
    Idea AP Idea Hariyana


    The output should return as much rows as table B hava.

    Any help is highly appreciated.
    Thanks in advance
    Navneet
  • #764078
    you can try the sub query

    Try the following query

    SELECT
    (SELECT brand
    FROM tableA
    WHERE tableB.mcc1 = tableA.mcc and tableB.mnc1 = tableA.mnc) brand1,

    (SELECT circle
    FROM tableA
    WHERE tableB.mcc1 = tableA.mcc and tableB.mnc1 = tableA.mnc) circle1,

    (SELECT brand
    FROM tableA
    WHERE tableB.mcc2 = tableA.mcc and tableB.mnc2 = tableA.mnc) brand2,

    (SELECT circle
    FROM tableA
    WHERE tableB.mcc2 = tableA.mcc and tableB.mnc2 = tableA.mnc) circle2

    FROM tableB

    By Nathan
    Direction is important than speed


Sign In to post your comments