CODEWITHSUNDEEP
sqlsql-server
Pavan
Pavan

Reputation: 13

SQL Server Query similar to VLOOKUP logic in excel

Table A:

Sample Table:

╔═══════════╦════════════════╦═══════╗
║ AccountID ║  AccountType   ║ Agent ║
╠═══════════╬════════════════╬═══════╣
║  413393   ║  Invoice       ║  A    ║
║  417811   ║  Credit        ║  NULL ║
╚═══════════╩════════════════╩═══════╝

Table B:

Sample Table:

╔═══════════╦════════════════╦
║ AccountID ║  Ref_AccountID ║ 
╠═══════════╬════════════════╬
║  413393   ║  NULL          ║  
║  417811   ║  413393        ║  
╚═══════════╩════════════════╩

Description: If the AccountType is invoice, then there would be a Agent associated with it. From Table A, you can see that it is associated with Agent A.

Current Output:

╔═══════════╦═════════════╦═══════════════╦═══════╗
║ AccountID ║ AccountType ║ Ref_AccountID ║ Agent ║
╠═══════════╬═════════════╬═══════════════╬═══════╣
║  413393   ║ Invoice     ║    NULL       ║ A     ║
║  417811   ║ Credit      ║    413393     ║ NULL  ║
╚═══════════╩═════════════╩═══════════════╩═══════╝

Expected Output:

╔═══════════╦═════════════╦═══════════════╦═══════╗
║ AccountID ║ AccountType ║ Ref_AccountID ║ Agent ║
╠═══════════╬═════════════╬═══════════════╬═══════╣
║  413393   ║ Invoice     ║    NULL       ║ A     ║
║  417811   ║ Credit      ║    413393     ║ A     ║
╚═══════════╩═════════════╩═══════════════╩═══════╝

The Agent should be displayed based on the Ref_AccountID. In this example, the Ref_AccountID is 413393, and for this AccountID in table A, the Agent is "A".

Thanks

Upvotes: 1

Views: 3316

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522741

This might be the answer you are seeking. I have included a complete query. It first joins tables A and B together, and then does a self join back to table A. From your updated information, it appears that when a record has a null value for the Agent column, you want to instead use the Agent value corresponding to the record whose AccountID matches the Ref_AccountID of the former record with the null Agent. The self join is necessary in the query because it makes this alternative value for the Agent potentially available in each record with a possible null Agent column. Here is the query:

SELECT t1.AccountID, t1.AccountType, t2.Ref_AccountID,
    CASE WHEN t1.Agent IS NOT NULL THEN t1.Agent ELSE t3.Agent END AS Agent
FROM TableA t1 INNER JOIN TableB t2 ON t1.AccountID = t2.AccountID
LEFT JOIN TableA t3 ON t2.Ref_AcccountID = t3.AccountID

Here is a working SQL Fiddle where you can test this query with the test data you gave in your original question.

Upvotes: 1

Mick
Mick

Reputation: 6864

You're looking for 

SELECT * FROM yourTable
WHERE
AccountID = 466361

Look here for more examples:

https://msdn.microsoft.com/en-us/library/ms187731.aspx

Upvotes: 0

Related Questions