• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 165
  • Last Modified:

question about inner join syntax

I have been given this pseudo code in order to write my query. The pseudo code is from someone who is not really strong at SQL, however this persons hase a pretty good idea of what she wants.
So here is the pseudo code.
UPDATE Customer
SET Customer.Id = CopyCust.Id,
    Customer.IsCurrent = CopyCust.IsCurrent
   FROM Customer
   INNER JOIN CopyCust
  ON
Customer.Code = CopyCust.Code
Customer.SSN  = CopyCust.SSN
Customer.LastName = Customer.FirstName

I don't think you can join on several columns at one time. So, What I really think she wanst is the following solution: (What do you think Experts)

UPDATE Customer
SET Customer.Id = CopyCust.Id,
    Customer.IsCurrent = CopyCust.IsCurrent
FROM Customer
INNER JOIN CopyCust
ON
Customer.Code = CopyCust.Code
WHERE
Customer.SSN  = CopyCust.SSN
AND Customer.LastName = Customer.FirstName
0
brgdotnet
Asked:
brgdotnet
3 Solutions
 
dsackerContract ERP Admin/ConsultantCommented:
Actually you can join on many columns, but you would separate them with AND statements, as follows:
UPDATE Customer
SET Customer.Id = CopyCust.Id,
    Customer.IsCurrent = CopyCust.IsCurrent
FROM Customer
INNER JOIN CopyCust
  ON  CopyCust.Code = Customer.Code
  AND CopyCust.SSN  = Customer.SSN
WHERE Customer.LastName = Customer.FirstName

Open in new window

I put line 8 into its own WHERE clause, since it has nothing to do with the CopyCust table.
0
 
Anthony PerkinsCommented:
You should get in the habit of using aliases as in:
UPDATE  c
SET     Id = cc.Id,
        IsCurrent = cc.IsCurrent
FROM    Customer c
        INNER JOIN CopyCust cc ON c.Code = cc.Code
                                  AND c.SSN = cc.SSN
WHERE   c.LastName = c.FirstName

Open in new window

0
 
dsackerContract ERP Admin/ConsultantCommented:
Brgdotnet, for small queries (and optionally for larger one), you can lose readability with aliases. They have their place, but for what you're doing, your original code was clean, easy to read and easy to advise on what to fine-tune.

I suggest you stay with your aesthetic and clean way of writing code, unless you decide for yourself to use abbreviations as aliases. They buy you nothing besides trading readability for keystrokes.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Vitor MontalvãoMSSQL Senior EngineerCommented:
As it can be used all conditions in the INNER JOIN clause, separating them with the AND operator:
INNER JOIN CopyCust   ON
 Customer.Code = CopyCust.Code
AND Customer.SSN  = CopyCust.SSN
AND Customer.LastName = Customer.FirstName


I would prefer to leave in the INNER JOIN only the relationship between tables (TableA.PK = TableB.FK) and in the WHERE clause the search criteria. The query plan will be the same but I think give you a better idea when you see the command. So, your suggestion may be right:
INNER JOIN CopyCust
 ON  Customer.Code = CopyCust.Code
'--> Relationship of the tables
 WHERE  Customer.SSN  = CopyCust.SSN AND Customer.LastName = Customer.FirstName '--> Search criteria
0
 
brgdotnetcontractorAuthor Commented:
Thank you.
0
 
Anthony PerkinsCommented:
They buy you nothing besides trading readability for keystrokes.
We will have to agree to disagree.  In my view the version I posted is far more readable.
0
 
dsackerContract ERP Admin/ConsultantCommented:
Igyftw.
0
 
Anthony PerkinsCommented:
I will have to take your word for it, as I have no idea what that means.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now