How to select data (two columns) from table based on a composite unique value and insert in another table?

  • I have a table CustomerMaster and the two fields in it - CustomerID and Source - give me unique values. The table already has a separate PK.

    Another table - Contracts - has two fields that map to the two fields in CustomerMaster: Contracts.CustomerNum maps to CustomerMaster.CustomerID, and Contacts.Source maps to CustomerMaster.Source.

    I want to get distinct rows from Contracts table, based on the combination of (CustomerNum & Source), and then append those values for CustomerNum and Source in CustomerMaster table (columns CustomerID and Source respectively).

    I'm able to use distinct in a query to get the unique records (select distinct (CustomerNum+Source) from Contracts) but I'm at sea as to how to select the values for CustomerNum & Source based on the distinct of (CustomerNum+Source).

    Any help will be appreciated.

  • Hi,

    Think this is what you're after, if I've read it right:

    SELECT c.CustomerNum, c.Source, COUNT(cm.*)

    FROM Contracts c

    INNER JOIN CustomerMaster cm ON cm.CustomerID = c.CustomerNum AND cm.Source = c.Source

    GROUP BY c.CustomerNum, c.Source

    Cheers

    Gaz

Viewing 2 posts - 1 through 1 (of 1 total)

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