What is the following query doing? Howelse can I write it?
SELECT distinct a.ORG_NAME, (SELECT DISTINCT a.org_name FROM fin.org_name a, fin.financial_institution b2 WHERE a.org_name_key = b.org_name_key AND b2.fi_key = b.fi_key ) "Co Name" FROM FIN.ORG_NAME a, FIN.FINANCIAL_INSTITUTION b, FIN.name_aliases na WHERE na.fi_key(+) = b.fi_key AND a.org_name_key(+) = na.org_name_key AND b.org_name_key in (705) ---- I added this to minimize the result set This is what the query fetches: ORG_Name: Andover Companies / Bay State Mutual Co Name: Bay State Insurance Company / Bay State Insurance Company The part I do not understand is the subquery that fetches Co Name. Is there any other way I can re-write this query. This was an oracle query that someoneelse wrote and now that the data is in SQL server, I am trying to develop a report, and I wanted to know what I am doing but this particular part is very confusing to me! Can one of the SQL gurus help me understand this please?
That's a correlated subquery. It's something you can do within T-SQL. Write another query that returns only a single value and make it a column. But it's not a terribly efficient way to do it. Sometimes the optimizer can make poor choices with that. You can rewrite it as a JOIN and it might be more efficient. I took a stab at it, but I'm unsure of your relationships and JOINs in there, so this might not be quite right. SELECT DISTINCT a.ORG_NAME, b3.CoName FROM FIN.ORG_NAME AS a LEFT JOIN FIN.name_aliases AS na ON a.org_name_key = na.org_name_key LEFT JOIN FIN.FINANCIAL_INSTITUTION as b ON na.fi_key = b.fi_key AND b.org_name_key in (705) JOIN (SELECT DISTINCT a.org_name AS CoName FROM fin.org_name a , fin.financial_institution b2 WHERE a.org_name_key = b.org_name_key AND b2.fi_key = b.fi_key ) AS b3 You've got old style ANSI-89 (that's 1989) JOIN syntax in there. I modified part of it to use modern JOIN syntax. I didn't modify that inner query because it's unclear to me what we're JOINing on. I hope that helps a little.