[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Comparing fields using SQL

Posted on 2009-05-18
3
Medium Priority
?
195 Views
Last Modified: 2012-05-07
I'm working on a query that will allow me to lookup fields (about 15 in all) in 2 different tables and attempt to ascertain if they match. If they do then I don't need to do anything but if not then I'll need to show certain fields.

Attached is a breakdown of the fields I need to lookup and match and the fields I need to report back on.

Any advice/assistance is greatly appreciated. I'm not sure if I've provided enough info here so happy to give whatever is needed.

Thanx
values.doc
0
Comment
Question by:Steven O'Neill
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24410809
what suppose to be the common field in two tables? email? or combination of some other fields?
0
 
LVL 2

Author Comment

by:Steven O'Neill
ID: 24411030
Hi there.

Perhaps this is a bit more complicated than I first made out. There is actually a third database/table that we need to use in order to obtain the common field here. The third table is called FilteredContact and the fields we use from here are:

contactID and new_clientreflegacy

We need to match FilteredContact.ContactID = PS_Contact_Base.ContactGUID (not one of the fields I've listed in the values.doc attachment) this gives me a complete GUID and we pickup the new_clientreflegacy at the same time. This we then use in a LEFT OUTER JOIN to a CustomerTable table which  is our third database. We match the first 10 characters of the FilteredContact.new_clientreflegacy field to the ANCHORSERV.S3CUSTDB.dbo.CustomerTable.CustomerNumber field and this will be our identifier.

You can see (hopefully) what I'm doing from the code below
SELECT     dbo.FilteredContact.contactid, dbo.FilteredContact.new_clientreflegacy, PS_Integration.dbo.PS_Contact_Base.ContactGUID, 
                      CustomerTable_1.CustomerNumber, PS_Integration.dbo.PS_Contact_Base.Created
FROM         dbo.FilteredContact INNER JOIN
                      PS_Integration.dbo.PS_Contact_Base ON dbo.FilteredContact.contactid = PS_Integration.dbo.PS_Contact_Base.ContactGUID LEFT OUTER JOIN
                      ANCHORSERV.S3CUSTDB.dbo.CustomerTable AS CustomerTable_1 ON SUBSTRING(dbo.FilteredContact.new_clientreflegacy, 1, 10) = CustomerTable_1.CustomerNumber

Open in new window

0
 
LVL 2

Accepted Solution

by:
Steven O'Neill earned 0 total points
ID: 25401494
We eventually used a different method for this. I've basically written a standard SQL statement and then we use SSRS to review the fields within the query. We then display the report with the problem fields highlighted. The idea was to have the query used within a report anyway so it made sense to simple have each field validate against each other within the SSRS report when it was being constructed.

A basic example of the query created is below:
SELECT * FROM (
SELECT     dbo.PS_Business_Base.Name AS [Business Name], dbo.PS_Business_Base.Region, dbo.PS_Business_Base.Status AS [Business Status], 
                      dbo.PS_Business_Base.CeasedTradingDate AS [Ceased Trading Date], dbo.PS_Business_Base.AddressLine1 AS [Address Line 1], 
                      dbo.PS_Business_Base.AddressLine2 AS [Address Line 2], dbo.PS_Business_Base.AddressLine3 AS [Address Line 3], dbo.PS_Business_Base.City, 
                      dbo.PS_Business_Base.Postcode, dbo.PS_Business_Base.County, dbo.PS_Business_Base.Email AS [Email Address], 
                      dbo.PS_Business_Base.Telephone AS [Business Telephone], dbo.PS_Business_Base.Fax AS [Business Fax], dbo.PS_Business_Base.Mobile, 
                      dbo.PS_Business_Base.BusinessStructure AS [Business Structure], dbo.PS_Business_Base.Description AS [Business Description], 
                      dbo.PS_Control.ValidationMessages AS [Reason For Failure - validation], dbo.PS_Control.ProposalCreationMessages AS [Reason For failure - Other], 
                      dbo.PS_Control.Completed AS [Process Date], dbo.PS_Control.UpdateType, dbo.PS_Control.ProcessFlag, dbo.PS_Business_Base.Created, 
                      dbo.PS_Control.Reference,
                      row_number() OVER ( partition BY dbo.PS_Control.Reference ORDER BY dbo.PS_Business_Base.Created DESC) rnum
FROM         dbo.PS_Business_Base INNER JOIN
                      dbo.PS_Control ON dbo.PS_Business_Base.BusinessGUID = dbo.PS_Control.Reference
WHERE     (dbo.PS_Control.UpdateType = 2) AND (dbo.PS_Control.ProcessFlag = 10)) temp
WHERE rnum = 1

Open in new window

0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question