Formula to compare data in view between 2 tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Formula to compare data in view between 2 tables

Author

Topic

blund
Starting Member

23 Posts

Posted - 2012-02-06 : 14:15:01

I will try to explain this properly, but let me know if more information is needed.

I have a View created which contains a table of customers and a table of contacts, amongst other things. There can be one or several contacts that are linked to one customer in the customer table. Each contact has a company ID, that sigifies which customer it belongs to. Each Customer has one "Main" contact, so the Customer table has one column that lists a single contact ID, which is the "Main" contact.

The Tables contain, amongst other things
Customer:
CustomerName
CustomerID
ContactID

Contact:
ContactName
ContactID
CustomerID

The results look something like:

CustomerName : ContactName

CompanyA : Bill Smith
CompanyA : Jill Anderson
CompanyB : Jon Doe
CompanyB : Ann Jones
CompanyB : Mark Peterson

Can I create another coloumn via a formula or expression, that would
compare, in that specific row, the Contact ID in Customer table to Contact ID in Contact table so that the 3rd column would say something like "Main" if the two ID's match, and "No" if they do not match.

Something like:

CustomerName : ContactName : [Customer]ContactID=?[Contact]ContactID

CompanyA : Bill Smith : Main
CompanyA : Jill Anderson : No
CompanyB : Jon Doe : No
CompanyB : Ann Jones : Main
CompanyB : Mark Peterson : No

Thank you

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-06 : 15:03:08

[code]
SELECT c.*,CASE WHEN ct.ContactID IS NULL THEN 'No' ELSE 'Main' END
FROM Customer c
LEFT JOIN Contact ct
ON ct.ContactID = c.ContactID
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

blund
Starting Member

23 Posts

Posted - 2012-02-06 : 17:14:16

Thank you for the code. I am not quite sure how to incorporate it, into my existing code.
If this the format of my code below, where should I insert your suggestion?
I greatly abbreviated the code above in my original post, to hopefully simply my question...
FYI, "cicmpy" is customer, "cicntp" is contact. The last 2 fields in the select area are the 2 fields I am trying to compare and create the output column depending on if they are the same or not.
Also, I am using Management Studio.

SELECT dbo.cicmpy.cmp_name, dbo.cicntp.cnt_f_name, dbo.cicntp.cnt_l_name, dbo.cicntp.FullName, dbo.cicntp.cnt_email, dbo.cicntp.cnt_job_desc,
dbo.Addresses.AddressLine1, dbo.Addresses.AddressLine2, dbo.Addresses.City, dbo.Addresses.StateCode, dbo.Addresses.PostCode, dbo.Addresses.Country,
dbo.cicmpy.textfield2 AS Sell_Status, dbo.cicntp.YesNoField1 AS [Contact 1=Do Not Mail], dbo.cicmpy.YesNofield1 AS [Company 1=Do Not Mail],
dbo.cicmpy.cmp_type AS Company_Type, dbo.cicmpy.cnt_id AS [Company Contact Main ID], dbo.cicntp.cnt_id AS [Contact ID]

FROM dbo.cicmpy INNER JOIN
dbo.cicntp ON dbo.cicmpy.cmp_wwn = dbo.cicntp.cmp_wwn INNER JOIN
dbo.Addresses ON dbo.cicntp.cnt_id = dbo.Addresses.ContactPerson

WHERE (dbo.Addresses.Type = N'POS') AND (NOT (dbo.cicmpy.cmp_type = N'S')) AND (NOT (dbo.cicmpy.cmp_status = N'E')) AND (dbo.cicntp.active_y = 1)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-06 : 18:24:07

[code]
SELECT dbo.cicmpy.cmp_name, dbo.cicntp.cnt_f_name, dbo.cicntp.cnt_l_name, dbo.cicntp.FullName, dbo.cicntp.cnt_email, dbo.cicntp.cnt_job_desc,
dbo.Addresses.AddressLine1, dbo.Addresses.AddressLine2, dbo.Addresses.City, dbo.Addresses.StateCode, dbo.Addresses.PostCode, dbo.Addresses.Country,
dbo.cicmpy.textfield2 AS Sell_Status, dbo.cicntp.YesNoField1 AS [Contact 1=Do Not Mail], dbo.cicmpy.YesNofield1 AS [Company 1=Do Not Mail],
dbo.cicmpy.cmp_type AS Company_Type, dbo.cicmpy.cnt_id AS [Company Contact Main ID],dbo.cicntp.cnt_id AS [Contact ID],
CASE WHEN dbo.cicntp.cnt_id IS NULL THEN 'No' ELSE 'Main' END AS [Type]
FROM dbo.cicmpy LEFT OUTER JOIN
dbo.cicntp ON dbo.cicmpy.cmp_wwn = dbo.cicntp.cmp_wwn
AND (dbo.cicntp.active_y = 1)
LEFT OUTER JOIN
dbo.Addresses ON dbo.cicntp.cnt_id = dbo.Addresses.ContactPerson

WHERE (dbo.Addresses.Type = N'POS') AND (NOT (dbo.cicmpy.cmp_type = N'S')) AND (NOT (dbo.cicmpy.cmp_status = N'E'))
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

blund
Starting Member

23 Posts

Posted - 2012-02-06 : 18:42:06

Thank you once again, visakh16.
It sure is nice when the answer is much simpler than one would expect!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-06 : 20:14:14

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources