Post reply

Using DISTINCT within a JOIN

  • November 10, 2011 at 7:56 am

    #146622

    Hi,

    Does someone know how can distinct be used within a join to restrict duplicates ? The requirement that I have is to use distinct for the MG_BOOKING_EQUIPMENT_REQ_DETAIL table so that it doesnt generate duplicate records. DISTINCT needs to be used within the following statement:

    JOINMG_BOOKING_EQUIPMENT_REQ_DETAIL e ON b.BOOKING_ID = e.BOOKING_ID

    this statement is a part of the following joins:

    FROM MG_BOOKING b

    JOINMG_BOOKING_EQUIPMENT_REQ_DETAIL e ON b.BOOKING_ID = e.BOOKING_ID

    JOINMG_BOOKING_ITINERARY i ON b.BOOKING_ID = i.BOOKING_ID

    JOINMG_LOCATION l on l.location_cd=i.from_location_cd

    JOINMGS_BookingAllocation_Groups ba on b.BOOKING_OFFICE_CD=ba.Booking_office_cd

    JOINMGS_CurrentReportingVessels AS crv ON i.VESSEL_CD = crv.VESSEL_CD

    Any suggestions would be appreciated ?

    Thanks,

    Paul

  • November 10, 2011 at 8:05 am

    #1405784

    There are multiple ways to do such a thing. For example, (and depending on the required columns) you could make a CTE for the DISTINCT select on the table and then join to the CTE instead of the table.

    I say "and depending on the required columns" because if the wrong columns are selected, DISTINCT may not help.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • November 10, 2011 at 8:07 am

    #1405788

    Jeff Moden (11/10/2011)

    There are multiple ways to do such a thing. For example, (and depending on the required columns) you could make a CTE for the DISTINCT select on the table and then join to the CTE instead of the table.

    I say "and depending on the required columns" because if the wrong columns are selected, DISTINCT may not help.

    could you please show me this with an example ? the syntax is where I am after.

  • November 11, 2011 at 7:46 pm

    #1406624

    I can. The key here is to know why you're joining the detail table to the booking table. What information is it that you're trying to get and what is causing the duplication? In other words, why is it necessary to join to the detail table?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply