1

I will simplify my problem to make the core issue clear: I have a query that does TableA INNER JOIN TableB LEFT JOIN TableC.

The result of the left join is that in the result set, two of the columns might have NULL values in some rows. To fill in the missing values I have to loop over the result set and query another database that has the data (so it is not possible to join in the first place).

My question is: Is there a standard/optimised approach when we need to fill nulls of a result set after a left join?

halfer
  • 19,824
  • 17
  • 99
  • 186
Jim
  • 18,826
  • 34
  • 135
  • 254

2 Answers2

4

You can use COALESCE(...) (MSDN - COALESCE) instead.

You query will then look like:

select a, b, COALESCE(TableB.c, 'replacement value')
from TableA INNER JOIN TableB LEFT JOIN TableC ...

Add another join for your replacement table and put the column you want to replace NULL values in the COALESCE function in you don't want to use a static value.

Jonathan Hiben
  • 533
  • 7
  • 16
  • But I don't have the replacement value.I need to fill it in but to do that I loop over the result set and query another db – Jim Apr 14 '14 at 14:10
  • You can create _linked server_ to join your external DB in the query (if the DB is on the same server: `otherDB.Schema.Table`). – Jonathan Hiben Apr 14 '14 at 14:13
1

"To fill in the missing values I have to loop over the result set and query another database that has the data (so it is not possible to join in the first place)."

Consider a different solution then looping to fill in data.

1.Another database on the same server==easy. Just join using db.schema qualified names.

  1. Another database on a another server, still possibly depending on you network topography. Then join using server.db.schema qualified names.

  2. Consider replicating the data you need if you regularly need to do this.

Karl Kieninger
  • 8,841
  • 2
  • 33
  • 49
  • I can join tables from different databases in different machines? – Jim Apr 14 '14 at 20:25
  • Yes. You can join across servers if they are linked. http://technet.microsoft.com/en-us/library/ms188279.aspx – Karl Kieninger Apr 14 '14 at 20:31
  • I am not using MS-SQL but MySQL – Jim Apr 14 '14 at 20:37
  • Jim. Sorry, I misread that, especially after seeing user3426870's comment above. MySQL's FEDERATED engine provides similar functionality. http://stackoverflow.com/questions/5370970/how-to-create-linked-server-mysql – Karl Kieninger Apr 14 '14 at 20:43
  • I am using InnoDb.Thanks for mentioning this though. I did not know this – Jim Apr 14 '14 at 20:50