1

Table A:

  • AccountID (PK, int, not null)
  • Agent (varchar(50), null)
  • AccountType (varchar(50), null)

Sample Table:

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

Table B:

  • AccountID(int, not null) - This is the foreign key, and I am extracting the data from both the tables based on matching AccountID records.
  • Ref_AccountID (int, null)

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

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
Pavan
  • 13
  • 1
  • 4
  • That looks like one table to me not two..... – Mick Nov 11 '15 at 04:35
  • Possible duplicate of [SQL Server query with same functionality as Excel VLookup](http://stackoverflow.com/questions/16472517/sql-server-query-with-same-functionality-as-excel-vlookup) – Mick Nov 11 '15 at 04:38

2 Answers2

1

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.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • Thanks a lot Tim. However I am getting the following error when I run the query: "Conversion failed when converting the varchar value 'A' to data type int." – Pavan Nov 11 '15 at 04:40
  • 1
    I think `Ref_AccountID` is of integer type. You need to convert `Ref_AccountID` to `varchar`. `COALESCE(Agent, cast(Ref_AccountID as varchar(20))` – Pரதீப் Nov 11 '15 at 04:47
  • @Tim: Yes, it is of Integer Type. – Pavan Nov 11 '15 at 04:53
  • Hi Tim, The query ran fine, but the agent field still looks to be blank. – Pavan Nov 11 '15 at 05:01
  • 1
    @Pavan - Is there any empty strings in `Agent` column – Pரதீப் Nov 11 '15 at 05:02
  • @Pavan, if you have an empty string or null in the `Agent` column, `COALESCE` as I have used it will still give you an empty result. – Tim Biegeleisen Nov 11 '15 at 05:04
  • @MM93, The main data extract has more than 10K records, and most of the values are Blank/NULL for the Agent Field. I would like to retrieve values to the Agent field based on Ref_AccountID. So in my above example, the value for Agent in my second row should display as 'A' instead of NULL – Pavan Nov 11 '15 at 05:07
  • What is the logic by which you would decide to place `A` in the second record? – Tim Biegeleisen Nov 11 '15 at 05:09
  • If you look at the value in "Ref_AccountID", it is actually the "AccountID" value in another table. So AccountID "466361" links to Agent "A". Only if there is a value in the "Ref_AccountID" (NULL values can be ignored), then check this with AccountID and then place the value of the Agent. – Pavan Nov 11 '15 at 05:14
  • Please post your full table schema. I don't feel like I have enough to go by right now. – Tim Biegeleisen Nov 11 '15 at 05:17
  • @TimBiegeleisen, I have re-phrased the question, and provided some information of what I want to achieve. Schema details provided. Thanks – Pavan Nov 12 '15 at 04:09
  • Hi Tim, when I use the latest logic, it completely ignores the fist line and just prints the second line of my output what I used to get. And still the Agent is NULL : 417811 ║ Credit ║ 413393 ║ NULL – Pavan Nov 12 '15 at 05:49
  • I suspect that there may be a problem with your data. I can create a SQL Fiddle if you like, but even it works this won't help you on your end. – Tim Biegeleisen Nov 12 '15 at 05:52
  • Hi @Tim, can you please create a fiddle for me please. – Pavan Nov 13 '15 at 04:08
  • I created a SQL Fiddle. It turns out the second join should be a `LEFT JOIN` rather than an `INNER JOIN` to ensure that records with a `null` `Ref_AccountID` still are retained. – Tim Biegeleisen Nov 13 '15 at 04:23
0

You're looking for 

SELECT * FROM yourTable
WHERE
AccountID = 466361

Look here for more examples:

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

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
Mick
  • 6,527
  • 4
  • 52
  • 67