Path: utzoo!utgpu!news-server.csri.toronto.edu!mailrus!wuarchive!brutus.cs.uiuc.edu!apple!bionet!ames!dftsrv!dev!dgis!jkrueger
From: jkrueger@dgis.dtic.dla.mil (Jon)
Newsgroups: comp.databases
Subject: Re: SQL Poser
Message-ID: <jkrueger.644204650@dgis.dtic.dla.mil>
Date: 1 Jun 90 01:44:10 GMT
References: <6588@umd5.umd.edu>
Organization: Defense Technical Information Center (DTIC), Alexandria VA
Lines: 75

jay@umd5.umd.edu (Jay Elvove) writes (paraphrased):

> consider a table
>
> 	+-----+
>	| emp |  emp    mgr
>	+-----+------+------+
>	      |   F  |   D  |
>	      |   G  |   D  |
>	      |   D  |   B  |
>	      |   B  |   A  |
>	      |   E  |   C  |
>	      |   C  |   A  |
>	      +------+------+
>
> representing the hierarchy
>
>                                  A
>                                 / \
>                                B   C
>                               /     \
>                              D       E
>                             / \
>                            F   G
>
> it may be assumed that each employee has exactly one manager;
> how to write a SQL query that finds the chain of command for
> an employee, e.g. given F, the chain of command is D B A

SQL (and other query languages for RDBMS) can't express recursive
queries.  So the simple answer is it can't be done.  The longer answer
is (1) it can't be done in a single query, (2) nor any finite number of
queries, (3) nor any reasonable number of queries, where "reasonable"
is defined as spending more time executing queries than dispatching
them, (4) nor without writing a program in some general purpose
programming language (see below).

-- Jon

#include <stdio.h>
char	*malloc();

main(argc, argv)
int	argc;
char	*argv[];
{
	setup();
	if (argc > 1)
		gen_chain(argv[1]);
	else
		fprintf(stderr, "usage: %s employee\n", argv[0]);
	cleanup();
}

#define	SIZE	50		/* column width, longest emp name */
##gen_chain(the_emp)
##char	*the_emp;
##{
##	char	the_mgr[SIZE];
##	int	mcount;

/* exec sql select count (mgr) into :mcount from emp where mgr = the_emp */
##	retrieve (mcount = count(emp.mgr where emp.emp = the_emp))
	if (mcount == 0) {
		printf("%s is at the top\n", the_emp);
	} else {
		/* select mgr into :the_mgr from emp where mgr = the_emp */
##		retrieve (the_mgr = emp.mgr) where emp.emp = the_emp
		printf("%s is the manager of %s\n", the_mgr, the_emp);
		gen_chain(the_mgr);
	}
##}
-- 
Jonathan Krueger    jkrueger@dtic.dla.mil   uunet!dgis!jkrueger
Drop in next time you're in the tri-planet area!