Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

How to select data (two columns) from table based on a composite unique value and insert in another table? Expand / Collapse
Posted Monday, October 22, 2012 3:01 PM


Group: General Forum Members
Last Login: Thursday, August 21, 2014 9:20 AM
Points: 16, Visits: 47
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.

Post #1375739
Posted Tuesday, October 23, 2012 4:47 AM


Group: General Forum Members
Last Login: Yesterday @ 9:21 AM
Points: 2,751, Visits: 5,187
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

Post #1375956
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse