Re: Hierarchic tables in Queries ??

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Mischa Sandberg (mischa_sandberg_at_telus.net)
Date: 07/13/04 

Date: Tue, 13 Jul 2004 19:23:52 GMT

Surprised that Joe Celko hasn't picked up on this thread yet, given the
topic.
I don't think there's anything you could want to know about hierarchies and
trees in SQL
that he hasn't covered already.

"Claude Vernier" <ClaudeVernier@discussions.microsoft.com> wrote in message
news:8ED828FF-C7AF-4150-9ABA-57E8E830319E@microsoft.com...
> Hello,
>
> I'm used since several years to create tables, stored procs and inner
joins...
> So, I can tell that I know SQL syntax a little bit, still I would need
help on this one.
>
> The client gave me a CSV file of subject:
>
> PARENT ID Name_E Name_F
> ------ ----- ------ ------
> 1024 1 Groceries ...
> 1024 2 Tools ...
> 1 3 Meat ...
> 1024 4 Car ...
>
> Where 1024 is the only case where PARENT does not exist as ID.
> I will have to access a list of items and stored them under the right
theme.
>
> For example:
>
> THEME ID Name_E Name_F
> ------ ----- ------ ------
> 3 1 Ham ...
> 2 2 hammer ...
>
> So, I need to know if I should adapt the first table to include the items
> or create another tables.
>
> Most important, I'm looking for a query that would return me
> all the first level THEME (ie: meaning they have 1024 as PARENT) but only
those
> that have an item below at any level.
>
> For example:
> The query would return only Groceries because Meat contains Ham and Tools
> because it has the hammer but not the Car.
>
> I guess that this kind of structure is common in the industry so I could
get some pointers and ideas.
> I would help me a lot.
>
> I'm french canadian living in Paris, so please excuse my english, if i'm
not clear enough,
> don't hesitate to tell me.
>
> Thanks a lot,
> Claude Vernier.
>

Relevant Pages

  • Re: Hierarchic tables in Queries ??
    ... "Mischa Sandberg" wrote: ... >> I'm used since several years to create tables, stored procs and inner ... >> So, I can tell that I know SQL syntax a little bit, still I would need ... >> The query would return only Groceries because Meat contains Ham and Tools ...
    (microsoft.public.sqlserver.programming)
  • Re: SQL REPLACE function does not work thru ADO/DAO/OLE layer
    ... It happened because REPLACE function is not a part of SQL syntax for the ... Access, but VBA function. ... query in a database and call it from the application as a stored procedure. ... > Microsoft DAO 3.6 ...
    (microsoft.public.vb.database.ado)
  • Re: script to retrieve active users that are not in a specific OU
    ... You are using SQL syntax in your query, which is fine, but there is no way I ... ' Comma delimited list of attribute values to retrieve. ... container, assuming that is the only container in your domain with users. ...
    (microsoft.public.windows.server.scripting)
  • Re: Execution plan different between the two SQL server with the same query
    ... Well that’s another reason to use stored procs:). ... Well you can still use sp_recompile and specify the name of the table or view that the query uses. ... Or look into using a PLAN GUIDE for the current call. ... not a stored procedure, I can't use recomply. ...
    (microsoft.public.sqlserver.server)
  • Re: Best Practise For Updates between Access 2000 and SQL Server
    ... If you are not familiar with SQL syntax, ... Change the query type to Update. ... cells with the names of the related columns from tblB. ... > ie UPDATE dtblActivities ...
    (microsoft.public.access.queries)