SQL server questions: names

Showing posts with label names. Show all posts

Tuesday, March 20, 2012

A tree of location and site names..

Hello,
This is a tuff one not sure if it can be done with an SQL query, I'm
thinking along the lines of an inner join, but I'm not really sure.
I have a table that is used to refer to location_names as nodes and sites as
the leaf nodes it is the site names that I want to return based on a join to
another table using the SiteFK column.
TABLE: TRS_SiteTree
SiteTreePK ParentFK Name SiteFK Path
1 0 All 0
0
2 1 South 0
0.0
3 1 North 0
0.1
4 2 Oxfordshire 0
0.0.0
5 4 Witney 1
0.0.0.0
6 4 Banbury 2
0.0.0.1
7 3 Teeside 0
0.1.0
8 7 Yarm 3
0.1.0.0
9 4 Oxford 4
0.0.0.2
10 3 Yorkshire 5
0.1.1
The SiteFK indicates if the site is a leaf node if it is <> 0 otherwise a
parent
The Path indicates the level of nesting
The leaf node Yorkshire has a parentFK of 3 which matches to the SiteTreePK
of 3 which subsequently has a Parent FK of 1 and a location of North which
sits under All where the Parent FK matches with the SiteTreePK of 1 All.
An illustration of the tree that the table represents:
0 All
|_____0.0 South
| |______0.0.0 Oxfordshire
| |_________ 0.0.0.0 Witney
(leaf 1)
| |_________ 0.0.0.1 Banbury
(leaf 2)
| |_________ 0.0.0.2 Oxford
(leaf 4)
|_____ 0.1 North
|______ 0.1.0 Teeside
| |_________ 0.1.0.0 Yarm (leaf
3)
|______ 0.1.1 Yorkshire (leaf 5)
if you have any suggestions on where I might start with this task this would
be muchly appreciated even if it's just a few ideas to try out.
Thank you kindly
Rhonda
ooops, I mean a self join
"Rhonda Fischer" wrote:

> Hello,
> This is a tuff one not sure if it can be done with an SQL query, I'm
> thinking along the lines of an inner join, but I'm not really sure.
> I have a table that is used to refer to location_names as nodes and sites as
> the leaf nodes it is the site names that I want to return based on a join to
> another table using the SiteFK column.
> TABLE: TRS_SiteTree
> SiteTreePK ParentFK Name SiteFK Path
> 1 0 All 0
> 0
> 2 1 South 0
> 0.0
> 3 1 North 0
> 0.1
> 4 2 Oxfordshire 0
> 0.0.0
> 5 4 Witney 1
> 0.0.0.0
> 6 4 Banbury 2
> 0.0.0.1
> 7 3 Teeside 0
> 0.1.0
> 8 7 Yarm 3
> 0.1.0.0
> 9 4 Oxford 4
> 0.0.0.2
> 10 3 Yorkshire 5
> 0.1.1
> The SiteFK indicates if the site is a leaf node if it is <> 0 otherwise a
> parent
> The Path indicates the level of nesting
> The leaf node Yorkshire has a parentFK of 3 which matches to the SiteTreePK
> of 3 which subsequently has a Parent FK of 1 and a location of North which
> sits under All where the Parent FK matches with the SiteTreePK of 1 All.
> An illustration of the tree that the table represents:
> 0 All
> |_____0.0 South
> | |______0.0.0 Oxfordshire
> | |_________ 0.0.0.0 Witney
> (leaf 1)
> | |_________ 0.0.0.1 Banbury
> (leaf 2)
> | |_________ 0.0.0.2 Oxford
> (leaf 4)
> |_____ 0.1 North
> |______ 0.1.0 Teeside
> | |_________ 0.1.0.0 Yarm (leaf
> 3)
> |______ 0.1.1 Yorkshire (leaf 5)
> if you have any suggestions on where I might start with this task this would
> be muchly appreciated even if it's just a few ideas to try out.
> Thank you kindly
> Rhonda
>
|||On Fri, 13 May 2005 08:22:02 -0700, Rhonda Fischer wrote:

>Hello,
>This is a tuff one not sure if it can be done with an SQL query, I'm
>thinking along the lines of an inner join, but I'm not really sure.
(snip)
Hi Rhonda,
Sorry for the late reply.
I'm not sure what exactly you're asking. Your message doesn't appear to
have any specific question. Are you looking for generic advice if your
solution is the best one for your problem? Or do you actually need a
query to do a specific job?
In the latter case, please repost. I'd appreciate it if you'd post the
table structure as a CREATE TABLE statement (including all constraints
and properties - any irrelevant columns may be left out, though) and the
sample data as INSERT statements. Also, post the output you'd like to
get from the sameple data.
Check www.aspfaq.com/5006 for some useful hints on how to post.
If you're looking for generic advice on how to deal with hierarchies,
then your best bet is to buy and read "Trees and Hierarchies in SQL" by
Joe Celko. This book describes several ways to model a hierarchy in SQL,
with their pros and cons.
You could also google for "nested sets model" and "adjacency list model"
to find information about two of the most common ways to represent a
hierarchy.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Dear Hugo,
Thank you very much for your considered thought on my questions. Your
thoughts and suggested reference material were helpful to draw this to a
resolution.
Thank you again

Cheerio
Rhonda
"Hugo Kornelis" wrote:

> On Fri, 13 May 2005 08:22:02 -0700, Rhonda Fischer wrote:
> (snip)
> Hi Rhonda,
> Sorry for the late reply.
> I'm not sure what exactly you're asking. Your message doesn't appear to
> have any specific question. Are you looking for generic advice if your
> solution is the best one for your problem? Or do you actually need a
> query to do a specific job?
> In the latter case, please repost. I'd appreciate it if you'd post the
> table structure as a CREATE TABLE statement (including all constraints
> and properties - any irrelevant columns may be left out, though) and the
> sample data as INSERT statements. Also, post the output you'd like to
> get from the sameple data.
> Check www.aspfaq.com/5006 for some useful hints on how to post.
> If you're looking for generic advice on how to deal with hierarchies,
> then your best bet is to buy and read "Trees and Hierarchies in SQL" by
> Joe Celko. This book describes several ways to model a hierarchy in SQL,
> with their pros and cons.
> You could also google for "nested sets model" and "adjacency list model"
> to find information about two of the most common ways to represent a
> hierarchy.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>

Monday, March 19, 2012

A to Z table data

Hello

I am currently using ms sql 2000 and I want to display my table, column of names in alphabetical order. How can I achieve this?

Thanks

Laura

Did you mean to dispaly all column names of a table in alphabetical order? In T-SQL we use 'ORDER BY' clause in query to perform ordering. So let's use such a statement to achieve your request:

select * from syscolumns where id=object_id('myTable') order by name

|||

Thanks for the reply. Thanks also for the solution.

I was actually thinking about when I had previously created a database using Access. It had a nice easy to use feature that could be used on a column whilst building the database. When you click on the column within the database you can choose to display in assending or decending order. I was looking for such a feature in SQL but so far I have not found it. Does this feature exist? If so where is it?

Thanks

Laura

|||

Sure there is. In SQL we use 'ORDER BY' clause to sort result. You can also sort result returned in Enterprise Manager: Just rigth click a table->choose 'Open Table'->'Query', then in the 'Diagram Pane' add some columns, and you can choose 'Sort Type'&'Sort Order' for each column. Then click 'Run' to execute the query. You can press F1 in 'Diagram Pane' to get more help from SQL2000 Books Online.

A strange problem with SQL query fro getting field names

Hello All,

I have been trying to get this code work, but I could not. Every thing seems going well. However, The result of running the sql query is strange. It shows the field names twice.
Eg:) if you have a table called "newtable" that has two fields[Custnumber, Custname], you will get somthing like this [Custnumber, Custname Custnumber, Custname]. I have tried many times, but I couldn't fix it.

 Sub Page_Load(sender As Object, e As EventArgs) handles Mybase.Load
 if not page.Ispostback then
 try
 Sqlconnection = New Sqlconnection (connectionString)
 querystring = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNs
 WHERE TABLE_NAME = 'Newtable'"
 SqlCommand = New SqlCommand(queryString, Sqlconnection)
 SqlConnection.Open
 dataReader = SqlCommand.ExecuteReader(CommandBehavior.CloseConnection)
 while dataReader.Read()
 Tablefields_txt.text += dataReader.Getstring(0) & ", "
 End while
 catch ex as Exception
 msgbox("An error has occured: " + ex.Message,0, "Error Message")
 finally
 SqlConnection.Close()
 End try
End if

Any help , pleaseCheck this bit. I assume this might have an impact on your problem.

Tablefields_txt.text += dataReader.Getstring(0) & ", "|||I have tried this:
Dim temp as string
while dataReader.Read()

temp += dataReader.Getstring(0) & ", "

End while

Tablefields_txt.text = temp

I think the problem might be from the querystring "select ....." , but i do know how to deal with it . I need help|||As I posted in your other thread, the problem is due to the "handles Mybase.Load". Remove this and you should see the results you expect.

Terri

Saturday, February 11, 2012

a heirarchical query

pls anybody help me with this.

i need to make a query where i have to display all names of a category
heirarchically.
C1-->C2-->C3-->C4

where C1 is the top level category

it shud b displayed as C1/C2/C3/C4

Also there can b any no of category levels.

pls anybody help me

manuUse Order By Fielda, FieldB, FieldC.

manu_ashok@.yahoo.com (Manu Ashok) wrote in message news:<54b28501.0404120702.60317431@.posting.google.com>...
> pls anybody help me with this.
> i need to make a query where i have to display all names of a category
> heirarchically.
> C1-->C2-->C3-->C4
> where C1 is the top level category
> it shud b displayed as C1/C2/C3/C4
> Also there can b any no of category levels.
> pls anybody help me
> manu|||dear rowan
abt that heirarchical query, the no of levels are not known. also the
table has name & immediate parent id in it.
pls do help me.
how do i use order by when i do not know the levels.
i'm a newbie to sql & asp
manu

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Use Order By Fielda, FieldB, FieldC.

manu_ashok@.yahoo.com (Manu Ashok) wrote in message news:<54b28501.0404120702.60317431@.posting.google.com>...
> pls anybody help me with this.
> i need to make a query where i have to display all names of a category
> heirarchically.
> C1-->C2-->C3-->C4
> where C1 is the top level category
> it shud b displayed as C1/C2/C3/C4
> Also there can b any no of category levels.
> pls anybody help me
> manu|||What are the fields in your table and what do you want the output to look like?

Manu Ashok <manu_ashok@.yahoo.com> wrote in message news:<407cd063$0$202$75868355@.news.frii.net>...
> dear rowan
> abt that heirarchical query, the no of levels are not known. also the
> table has name & immediate parent id in it.
> pls do help me.
> how do i use order by when i do not know the levels.
> i'm a newbie to sql & asp
> manu
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!|||dear rowan
abt that heirarchical query, the no of levels are not known. also the
table has name & immediate parent id in it.
pls do help me.
how do i use order by when i do not know the levels.
i'm a newbie to sql & asp
manu

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||What are the fields in your table and what do you want the output to look like?

Please visit following url:

http://www.winnetmag.com/SQLServer/...threadid=116492

May be it will help you

Regards

Saghir Taj
MCDBA

phantomtoe@.yahoo.com (Rowan) wrote in message news:<4bbf8d70.0404141504.30aa2a95@.posting.google.com>...
> What are the fields in your table and what do you want the output to look like?
> Manu Ashok <manu_ashok@.yahoo.com> wrote in message news:<407cd063$0$202$75868355@.news.frii.net>...
> > dear rowan
> > abt that heirarchical query, the no of levels are not known. also the
> > table has name & immediate parent id in it.
> > pls do help me.
> > how do i use order by when i do not know the levels.
> > i'm a newbie to sql & asp
> > manu
> > *** Sent via Developersdex http://www.developersdex.com ***
> > Don't just participate in USENET...get rewarded for it!|||Dear