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

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server Newbies » How to select data (two columns) from table...

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

Rate Topic

Display Mode

Topic Options

Author

Message

SQL_beginner1

Posted Monday, October 22, 2012 3:01 PM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, November 29, 2012 2:38 PM
Points: 6, Visits: 20

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

Gazareth

Posted Tuesday, October 23, 2012 4:47 AM

UDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 6:22 AM
Points: 1,474, Visits: 2,341

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

Post #1375956

« Prev Topic | Next Topic »

Permissions