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

• SQL_beginner1

  Right there with Babe

  Points: 796

  More actions

  October 22, 2012 at 3:01 pm

  #393440

  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.

• Gazareth

  One Orange Chip

  Points: 27737

  More actions

  October 23, 2012 at 4:47 am

  #1551823

  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