Sql query with Join

RIAS
RIAS used Ask the Experts™
on
Hello Experts ,
Please find the file attached , how do I get the expected result.

Thanks
Example6.xlsx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2008

Commented:
This should do it
SELECT A.Code, A.Ref, A.Auth, B.Name, B.Surname
FROM TableA A INNER JOIN TableB B ON A.Code = B.Code

Open in new window

SELECT B.CODE, A.REF,A.AUTH,B.NAME,B.SURNAME
FROM TABLEB B
LEFT JOIN TABLEA A ON B.CODE=A.CODE
MS SQL Server also allows older notation for table joins in FROM clause:
SELECT A.Code, A.Ref, A.Auth, B.Name, B.Surname
  FROM TableA A, TableB B 
 WHERE A.Code = B.Code

Open in new window

Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

But it seems you would need something different - UPDATE TableB rows by values from TableA:
DECLARE @TableA TABLE (Ref int, Auth char(3), Code char(5))
INSERT INTO @TableA VALUES (1, 'dfk', '45HJN'), (5, 'JOL', '78HJU')

DECLARE @TableB TABLE (Code char(5), Ref int, Auth char(3), Name varchar(20), Surname varchar(20))
INSERT INTO @TableB VALUES ('45HJN', null, null, 'Avc', 'hhuo'), ('78HJU', null, null, 'huo', 'uiu')

SELECT * FROM @TableB

UPDATE @TableB SET Ref = A.Ref, Auth = A.Auth
  FROM @TableB B
  JOIN @TableA A ON A.Code = B.Code

SELECT * FROM @TableB

Open in new window

Above example defines TableA and TableB in variables but you may use your own tables or temp tables.

Author

Commented:
Thanks!!!
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial