Home | Blog | Software | Reviews and Features | Forum | Help | Donate | About us
topbanner_forum
  *

avatar image

Welcome, Guest. Please login or register.
Did you miss your activation email?

Login with username, password and session length
  • September 01, 2015, 02:58:58 AM
  • Proudly celebrating 10 years online.
  • Donate now to become a lifetime supporting member of the site and get a non-expiring license key for all of our programs.
  • donate

Author Topic: SQL query newbie: Help me work through my first project?  (Read 1688 times)

superboyac

  • Charter Member
  • Joined in 2005
  • ***
  • Posts: 6,026
  • Is your software in my list?
    • View Profile
    • Donate to Member
SQL query newbie: Help me work through my first project?
« on: March 01, 2011, 10:13:44 AM »
Hi everyone, I've gotten my hands into programming now after not doing anything like this for maybe 10 years now.  I'm working with an Oracle database and I'm just starting to learn how to run SQL queries.  I've gotten the basics so far of the select, where functions.  But I'm trying to do a little more fancy things, and it's becoming difficult.  So I wouldn't mind getting coached throughout this thread.  Yeah?

First challenge:
I've been able to write a simple query using and/or statements.  Now, I want to group everything in a particular way to reflect the hierarchy that the database is representing.  So I need to somehow figure out how to group things according to the proper parents/children.  What I'm doing now is trying to figure out which columns control the hierarchy, because it's not clear, and I can't ask anyone here.

mouser

  • First Author
  • Administrator
  • Joined in 2005
  • *****
  • Posts: 34,967
    • View Profile
    • Mouser's Software Zone on DonationCoder.com
    • Donate to Member
Re: SQL query newbie: Help me work through my first project?
« Reply #1 on: March 01, 2011, 10:17:15 AM »
I think you might need to be a little more specific and maybe give some example data.

And furthermore, when you talk about grouping and hierarchy it sounds like you are talking about how to organize the data after it comes back from sql in whatever other language you are using -- since sql really will only ever return rows of flat data.  Or maybe you're talking about how to pull data from multiple tables based on what they call "foreign keys", where you are combining rows from different tables based on matching column values.  Either way, i think you'll need to be more specific.

superboyac

  • Charter Member
  • Joined in 2005
  • ***
  • Posts: 6,026
  • Is your software in my list?
    • View Profile
    • Donate to Member
Re: SQL query newbie: Help me work through my first project?
« Reply #2 on: March 01, 2011, 10:19:43 AM »
Sure, I just don't know how confidential everything is.  here's an example:
Quote
and DESCRIPTION like '%{%}%'
I'm trying to add a way for the query to return matches that only contain the bracket symbols "{" and "}" in the description column.  I have entries that have a code inside brackets like "{something here} some text", and I only want those returned.  So the brackets are the distinguishing feature.  But I just ran the query, and that and statement above didn't do anything.

superboyac

  • Charter Member
  • Joined in 2005
  • ***
  • Posts: 6,026
  • Is your software in my list?
    • View Profile
    • Donate to Member
Re: SQL query newbie: Help me work through my first project?
« Reply #3 on: March 01, 2011, 10:28:12 AM »
Ok, I got the above question resolved.  No questions for now.

superboyac

  • Charter Member
  • Joined in 2005
  • ***
  • Posts: 6,026
  • Is your software in my list?
    • View Profile
    • Donate to Member
Re: SQL query newbie: Help me work through my first project?
« Reply #4 on: March 08, 2011, 04:21:23 PM »
I'm having a real hard time trying to get a query to return some kind of outline numbering.  So, I've been able to arrange my query results in the proper hierarchical order using the Oracle hierarchical functions like:
Start with
connect by prior

So that's good.  Now I need to add a column showing the outline numbering, such as:
1
1.1
1.2
1.2.1
1.2.2
etc.

How do i do that?  I've tried using this:
SYS_CONNECT_BY_PATH

but the hard part is trying to get it to COUNT the items as it goes through the hierarchy.  The closest I've come to using the connect by path command above is to show the path to of the row in the hierarchy.  So I can get it to show something like:
Parent/Child1/Child2
But I'd like it to do the "1.2.1" kind of numbering instead.  Any thoughts?  I'm missing a way to make it count stuff.

CWuestefeld

  • Supporting Member
  • Joined in 2006
  • **
  • Posts: 975
    • View Profile
    • Revolution Calling
    • Donate to Member
Re: SQL query newbie: Help me work through my first project?
« Reply #5 on: March 26, 2011, 10:11:51 AM »
You've chosen just about the hardest thing to get a relational database to do :o. They really don't lend themselves well to hierarchical data, although it is possible with some work.

There are a number of ways to attack it, but it's highly dependent on the specifics of the problem, and varies greatly depending on the database engine being used (I'm a SQL Server expert, not Oracle).

My recommendation is to get a copy of Joe Celko's Trees and Hierarchies. He explains all your options, with pretty good examples.