[thelist] Nested Queries

[Steve Lewis]

Feingold Josh S wrote:


> Aaah, let SQL take care of things.  That's what I like.  I only wish I was
> better at complex (or at least what I don't know) queries.  I guess that
> bodes well for people like you ;).
>
> Let's say I constrain the nesting to four levels deep.  What would a query
> then look like?

I have a solution that I nabbed and adapted from Henderson's _The Guru's
Guide to Transact-SQL_ that involves some rather convoluted querying but
allows an arbitrary depth.  This query is allowing me to select client
accounts that signed up through an affiliate network.  I run it as three
separate queries delivered to the SQL server, encompassed inside a
transaction.

I start by building a temp table (this should be a view imho but I could
not get the view definition and following query to work on this one,
rudy: care to take a stab at this?) that we use to fill in all the
explicit and implicit links in the hierarchy structure, then a second
query selects the elements I actually wanted based on this complete
hierarchy view.

I agree you should probably have your groups define their parent rather
than have you groups define their children, otherwise you will have some
work to do.  Finally note that the second query expects your tree's root
affiliate (group, in your case) to have a parent_id = it's own id... not
a parent_id of 0 or -1 or whatever... this can be kinda important so
that you know where the search will end. :)

SELECT seq=IDENTITY(int), chartdepth=1, affiliate=o2.id,
parent_affiliate=o1.id
		INTO #org_chart
		FROM affiliate o1 JOIN affiliate o2 ON (o1.id=o2.parent_id)

		WHILE (@@rowcount > 0) BEGIN
			INSERT #org_chart (chartdepth, affiliate, parent_affiliate)
			SELECT DISTINCT o1.chartdepth+1, o2.affiliate, o1.parent_affiliate
			FROM #org_chart o1 JOIN #org_chart o2 ON (o1.affiliate=o2.parent_affiliate)
			WHERE o1.chartdepth=(SELECT MAX(chartdepth) FROM #org_chart)
			AND o1.parent_affiliate<>o1.affiliate
		END

		SELECT account.id AS account_id, website.domain_name, b.domain_name AS
affiliate_domain
		FROM #org_chart o JOIN affiliate a ON (o.parent_affiliate=a.id)
		INNER JOIN affiliate b ON (o.affiliate=b.id)
		INNER JOIN account ON b.id = account.affiliate_id
		WHERE a.id = '$session.partner_id' OR b.id = '$session.partner_id'
		ORDER BY seq

		DROP TABLE #org_chart

--Steve