1

I’m somewhat of a newbie to SQL queries, especially anything containing logic, and although I've searched for hours finding the exact terms to search for is not easy in this case! I have a relatively simple one, I’m sure:

A table has 2 columns, and each row contains data about a function in a program. Some functions have a parent function associated (for grouping). Column A is the unique function ID. Column B indicates, when applicable, the parent function’s ID. All parent function IDs are independent and valid function IDs that exist elsewhere in column A.

For reporting purposes I need to list the functions grouped by their parent ID, listing the parent function with the child functions. I can easily report by parent function ID, but the problem is that a parent function does not know that it is a parent function because its column B is empty!

What I need to do is complete the value in Column B if it is empty and the function is referenced elsewhere as a parent function.

Otherwise stated, for each row that has a null value in Column B:

  1. Take the value from column A
  2. Check for the existence of that value in ANY row on column B
  3. If there is a match, inject the value into column B (so that Column A and B have the same value)

What I have: (Query: SELECT function_id, parent_function FROM functions)

FUNCTION_ID   PARENT_FUNCTION   
4  
13            4  
79  
138           4
195
314           345
345

What I need to have:

FUNCTION_ID   PARENT_FUNCTION
4             4
13            4
79
138           4
195
314           345
345           345

Any Ideas? I can't wait to get more familiar with SQL! Thanks ahead of time.

alteredNate
  • 89
  • 2
  • 8
  • 1
    do you really want the values in the table updated, or just displayed in your output? – Beth Dec 10 '12 at 17:00
  • Sorry, I didn't see this before. New to the site! No, I don't want the values updated, I just need to results updated - I'm pulling them into excel and using pivotcharts. – alteredNate Dec 10 '12 at 20:50
  • @Beth I have a follow-up question if any of you are interested [link](http://stackoverflow.com/questions/13822936/sql-excel-query-parameter-with-a-join) – alteredNate Dec 11 '12 at 15:25

3 Answers3

1

This should work for you:

UPDATE functions
SET parent_function = function_id
WHERE parent_function IS NULL
AND function_id IN (SELECT parent_function FROM functions)

This will set parent_function equal to function_id where it has not yet been set, and where it appears somewhere in the parent_function column.

If you don't actually want to modify the table data but still return values that you need, you can use similar logic like this:

SELECT f.function_id, COALESCE(f.parent_function, f2.function_id) as parent_function
FROM functions f
LEFT JOIN functions f2
  ON f.function_id = f2.function_id
  AND f2.function_id IN (SELECT parent_function FROM functions)
PinnyM
  • 35,165
  • 3
  • 73
  • 81
  • This is perfect! I'm going to look up coalesce (as I said, really a beginner...). This will allow me to get my report ready, and I'll keep customizing my pivot table as needed. Thank you! – alteredNate Dec 10 '12 at 20:19
  • I have a [follow-up question](http://stackoverflow.com/questions/13822936/sql-excel-query-parameter-with-a-join) if you are interested – alteredNate Dec 11 '12 at 15:26
0

maybe you can compare the two table using EXCEPT or INTERSECT 

http://msdn.microsoft.com/en-us/library/ms188055.aspx

more tutorials>:

 http://www.mssqltips.com/sqlservertip/1327/compare-sql-server-datasets-with-intersect-and-except/
0

How's this look?

select distinct
t1.funx, t1.parent,
case when t2.parent is null then t1.parent
else t2.parent end as newparent
from 
tbl t1 left outer join
tbl t2 on 
t1.funx = t2.parent

sqlFiddle

Beth
  • 9,531
  • 1
  • 24
  • 43
  • Yes, this works, short and concise, and now I've learned how to use 'as' - great! However I don't need the extra column, the solution by PinnyM is just what I need! – alteredNate Dec 10 '12 at 20:17