Page 1 of 3 123 LastLast
Results 1 to 15 of 35
  1. #1
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10

    Cool Unanswered: Nested SQL(Nested SQL(Nested SQL(Nested SQL)))

    Can you give a whole SQL statement an alias so you can use it later?

    Eg.

    SELECT * FROM Employees WHERE age < 19
    -- Could I call the above statement something like 'statement1' to use below as shown

    SELECT * FROM Employees WHERE age < 25 AND NOT IN (statement1)


    Soin effect I get a nested statement.
    The reason I am asking about aliases is because this would need to be repeated for, E.g. age < 30 Then age < 35 and so on and so forth.

    So basically, I just want to alias a qhole SQL statement

    Any help would be greatly appreciated - George
    Last edited by gvee; 01-03-07 at 12:36.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the concept you are thinking of is called a view

    so, the answer is yes, you can
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Could you elaborate slightly please?
    Could you perhaps include an example of hwo to reference/alias/view an sql statement.

    Thanks mate

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Something like this?
    Code:
    CREATE VIEW stat_1 AS
      SELECT * FROM employees WHERE age < 19;
      
    CREATE VIEW stat_2 AS
      SELECT * FROM employees 
        WHERE age < 25
          AND employee_id NOT IN (SELECT employee_id FROM stat_1);

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10

    Red face

    I'm using a program called QUERY ANALYZER in which I am the following (and just to confuse I am now trying qualifications..

    Code:
    CREATE VIEW stat_1 AS
    SELECT e.employee_number
    FROM pwa_master.employee e, pwa_master.trgqual t
    WHERE t.qualification_level = 'ALEV'
      
    CREATE VIEW stat_2 AS
    SELECT e.employee_number, t.qualification_level
    FROM pwa_master.employee e, pwa_master.trgqual t
    WHERE t.qualification_level = 'GCSE'
    AND e.employee_number NOT IN (SELECT e.employee_number FROM stat_1)
    From which I get the following message:

    Code:
    Server: Msg 2714, Level 16, State 5, Procedure stat_1, Line 2
    There is already an object named 'stat_1' in the database
    I don't know what I have done, but it doesn't look good.

    Normally in this program if I do a select it only displays the results, which is all I want to do, not create anything in the database!
    Last edited by gvee; 01-04-07 at 07:50.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    if you run CREATE VIEW stat_1 more than once, guess what happens on the second attempt?

    a view is simple a query definition -- it's exactly what you want

    yes, the query definition gets stored in your database

    if you don't want to store the view definition in your database, you cannot use a view
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I dont want to store anything in the database, just use SQL to extract the data I need.

    So are there any other methods I can use instead of a view?

    P.S. When a view is created, where in the database is it stored?

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by georgev
    I dont want to store anything in the database, just use SQL to extract the data I need.
    you can always do that

    Quote Originally Posted by georgev
    So are there any other methods I can use instead of a view?
    depends on what you are really trying to do, which isn't really clear

    Quote Originally Posted by georgev
    P.S. When a view is created, where in the database is it stored?
    in the system tables
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Ok, I'll try and clarify.

    Employees table is linked to a table called Training and Qualifications.
    I have been asked to produce an SQL statement that selects an employees' highest qualification. Unfortunately I cannot do this with things such as child functions or even entry date as this will be innacurate.

    Unfortunately there is no ranking available in the database to signify which qualification is highest E.g. GCSE, ALevels, OLevels, Masters etc etc.

    So I thought that if I could say, have one SQL statement for each and gave them each an alias I could do the following:

    Code:
    SELECT employee_number
    FROM training_and_qualifications
    WHERE Qualification_Level = 'GCSE'
    AND Qualification_Level NOT IN [SQL Statement 2]
    the previous one would be of a similar format again, but GCSE could be replaced with 'ALEV' and used NOT IN [SQL Statement 3].

    Is that any better?
    If not - I'm sorry

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you should have posted your original requirements first, instead of asking how to accomplish something really complicated

    "I have been asked to produce an SQL statement that selects an employees' highest qualification"

    please give information about the ranking of these qualifications
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    That's the problem, there is no ranking.

    I would have to do it logically which is why I was building this sql statement.
    I would take the highest qualification (Masters I think) and that would be my very first SQL statement.

    Then I would perform another one for, say, ALevels and use "NOT IN MastersSQLStatement"

    Followed by GCSE (?) and use "NOT IN MastersSQLStatement OR NOT IN ALevelsSQLStatement"

    Sorry for complicating the issue.

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    if there is no ranking, then you could never pick the highest one, could you

    so of course there's a ranking

    you said yourself that Masters is the highest, but i bet Doctors is even higher, and Bachelors is next highest after Masters, and so on

    if an employee has several qualifications, how are they stored?

    you need to give information about your table structure
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    There is no ranking stored in the database, I am producing the order myself.
    I wouldn't have a problem if in the database each qualification has a rank number, but I am unable to amend the tables to do such a thing.

    training_and_qualifications table is linked to the employees table by the employees unique_identifier.

    In the training_and_qualifications table each row has its own unique_identifier.

    therefore one employee can have many qualifications, linked by the employee unique ID

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by georgev
    There is no ranking stored in the database, I am producing the order myself.
    i do realize there is no ranking stored in the database, however, if you don't share this information with me, i cannot help you

    what are the rankings?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    This discussion reminds me of something that happened ages ago when I was a teenager and worked in a service station. One morning I showed up to open up, and found a car parked on the driveway, no keys, with a note on the windshield that said "something is wrong".

    Looking at the car, my first thought was "Obviously" which was quickly followed by "What is this thing doing squarely in the middle of my driveway?" although the phrasing was a bit different.

    I'm sure that we can help you. I'm comfortable that your database design needs some small changes, specifically you need a table that includes all of the qualifications you use with a ranking for each of them.

    As we know that this uses Microsoft SQL Server, and is almost certainly using SQL 2000 instead of SQL 2005, that helps us too. In order to get more relevant comments, I'm going to move the whole thread to the Microsoft SQL Server forum where more people will find it and contribute to helping solve your problem(s).

    At the top of every page in the Microsoft SQL forum is a FAQ. In the FAQ is a post describing how to get fast and correct answers to your questions. Please read that post, the ideas there will help you get a faster and less frustrating answer for your question!

    -PatP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •