The scenario is. There exists a customer table with the logical customer information, along with a different table we are likely to call "event". In case table, amongst other things, would be the client's name, surname, and ssn.
Within the customer table, the ssn is really a varchar(9) intended just for numbers clearly, nevertheless the event table's ssn is varchar(11), meant for numbers plus dashes. The truth is a few of the clients might be people or organizations, and also the ssn can be used to differenciate between your as a result:
Individuals ssn format is 123-45-6789 while entities' format is 12-3456789.
What exactly I wish to do operates a question that joins the client and Event tables on the very first names AND last names to ensure that I'm able to look for discrepancies within the formatting of SSN in case table.
I am a relatively recent developer so I have never been confronted with this type of situation and do not determine if it were possible. This is what my current query appears like:
SELECT top 10 c.firstname, c.lastname, c.ssn, e.ssn, e.firstname, e.lastname FROM customer c with(nolock) LEFT OUTER JOIN [event] e with(nolock) on c.firstname = e.firstname and c.lastname = e.lastname
Yes, you should use multiple fields using any types of function, native or user defined, within the ON clause criteria. It's as you would employ within the WHERE clause, but situated inside a slightly different place. Good examples:
FROM TABLE_A a JOIN TABLE_B b ON b.col1 || ' ' || b.col2 = a.col
FROM TABLE_A a JOIN TABLE_B b ON ABS(b.col) = a.col
Will it matter in which the filtration happens?
ON clause, it does not matter if utilizing an INNER join. However it matters if utilizing an OUTER join, since the ON criteria is used before/throughout the JOIN as the WHERE happens after -- this could drastically alter the result set.
SELECT top 10 c.firstname, c.lastname, c.ssn, e.ssn, e.firstname, e.lastname FROM customer c with(nolock) JOIN [event] e with(nolock) on c.firstname = e.firstname and c.lastname = e.lastname
This question labored, it's just like the initial one however the left outer was giving me skewed results.
You can do it such as this:
SELECT TOP 10 c.firstname, c.lastname, c.ssn, e.ssn, e.firstname, e.lastname FROM customer c with(nolock), [event] e with(nolock) WHERE c.firstname = e.firstname AND c.lastname = e.lastname