Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    Join Date
    Feb 2008
    Posts
    120

    Unanswered: Select Statement Help Required

    Hi all....
    Can anyone help me out with a select statement?

    I have a 'categories' table and need to select all records where the ParentID equals a given value, eg. 137, but in the same statement i also need to select all records where the ParentID equals the CategoryID of the first part of the select statement.
    So the first part would give me records like this:

    CategoryID ParentID Category
    1 137 17th Edition Testers
    2 137 Clampmeters
    3 137 General Electricals

    The second part would give me records based on ParentID = CateogryID from the first part of the statement like.

    CategoryID ParentID Category
    9 1 Calibration Checkboxes
    10 1 RCD Testers
    11 1 Loop Testers
    19 2 Fluke Clampmeters

    etc....

    But i need all of these records from one select statement!
    Is this possible?

    Thanks
    Andy

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT CategoryID 
         , ParentID 
         , Category
      FROM categories
     WHERE ParentID = 137
    UNION ALL
    SELECT two.CategoryID 
         , two.ParentID 
         , two.Category
      FROM categories AS one
    INNER
      JOIN categories AS two
        ON two.ParentID = one.CategoryID
     WHERE one.ParentID = 137
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2008
    Posts
    120
    r937 - Thanks that works perfectly :-)

    Very much appreciated
    Andy

  4. #4
    Join Date
    Feb 2008
    Posts
    120

    A bit more help required please

    My requirements have changed slightly from my original post and i hope i can get some help on my 'new headache' :-)

    I need to pull the three levels (top, sub, sub sub) from my database for the menu system im making.
    At present im getting two levels.
    I've had a few attempts at at but so far haven't succeeded

    At present i'm using the following SQL :
    SELECT one.ParentID, one.Category AS Parent, one.CatID, one.CategoryID, two.Category, two.CategoryID
    FROM categories AS one INNER JOIN categories AS two ON two.ParentID=one.CategoryID
    WHERE one.ParentID=0 And one.CatID='e'
    ORDER BY one.Category;


    I have attached the categories table and SQL Query Result screengrabs to illustrate.

    Thanks in advance - fingers crossed

    Andy
    Attached Thumbnails Attached Thumbnails categories.gif   query_result.gif  

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    try this --
    Code:
    SELECT one.ParentID
         , one.Category AS Parent
         , one.CatID
         , one.CategoryID
         , two.Category
         , two.CategoryID
         , three.Category
         , three.CategoryID
      FROM (
           categories AS one 
    INNER 
      JOIN categories AS two 
        ON two.ParentID = one.CategoryID
           )
    INNER 
      JOIN categories AS three 
        ON three.ParentID = two.CategoryID
     WHERE one.ParentID = 0 
       AND one.CatID = 'e'
    ORDER 
        BY one.Category
         , two.Category
         , three.Category
    Last edited by r937; 05-21-10 at 11:03.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Feb 2008
    Posts
    120
    Thanks r937

    I'm getting an error when i run the query - please see attached screen grab
    Looks like you know what you're doing though.
    Hope you can see where the problem is

    Andy
    Attached Thumbnails Attached Thumbnails query_result2.gif  

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by AndyJay View Post
    I'm getting an error when i run the query
    you forgot to mention that you're using access

    i've edited my query to include the necessary parentheses that access requires when more than 2 tables are involved in a join
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Feb 2008
    Posts
    120
    r937 - you are a star :-)
    How the hell did you work it out?
    I need to read up more on SQL

    My local machine is using Access, but the live site is using MS SQL so both examples will be very useful.

    Thanks again - fantastic
    Andy

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by AndyJay View Post
    My local machine is using Access, but the live site is using MS SQL so both examples will be very useful.
    i urge you most sincerely to install SQL Server Express on your local machine so that you can test your queries properly

    there are numerous differences between Access SQL and SQL Server SQL

    save yourself a lot of future frustration and heartache
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Feb 2008
    Posts
    120
    Hi r937
    I took your advice and now have SQL Server 2008 Express on my local machine :-)
    I ran the Query in SQL Server without the extra parentheses and it gives the correct results for the subsub categories.

    Is there a way of getting all of the categories displaying in the three tables (parent, category, category)?
    This would allow me to create just one recordset instead of two.
    I've attached a screengrab of the subsub category results and another showing the desired results (subsub category needs to be in the correct order)
    Sorry to be such a pain.....
    How do you create your SQL queries?

    Thanks
    Andy
    Attached Thumbnails Attached Thumbnails query_sg.jpg   required_query_sg.jpg  

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by AndyJay View Post
    Is there a way of getting all of the categories displaying in the three tables (parent, category, category)?
    This would allow me to create just one recordset instead of two.
    sorry, i don't know what you're asking here

    Quote Originally Posted by AndyJay View Post
    How do you create your SQL queries?
    in UltraEdit (a text editor)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Feb 2008
    Posts
    120
    Thanks for the reply again r937 - sorry to take up your time. It's greatly appreciated.
    I've attached another screengrab. Hope it helps
    Thanks
    Andy
    Attached Thumbnails Attached Thumbnails subsub.jpg  

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by AndyJay View Post
    I've attached another screengrab.
    well, i can see that you're displaying a heirarchy to the 3rd level

    what was the question again?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Feb 2008
    Posts
    120
    Hi r937

    That screen grab is just two images stuck together, showing what i want to get out of the database :-)
    I would like to get this result using one statement and one recordset instead of two.
    The first SQL below displays the top and sub categories
    The second only displays the subsub categories

    Thanks again - owe you a beer



    SELECT one.ParentID
    , one.Category AS Parent
    , one.CatID
    , one.CategoryID
    , two.Category
    , two.CategoryID
    FROM categories AS one
    INNER
    JOIN categories AS two
    ON two.ParentID=one.CategoryID
    WHERE one.ParentID=0 And one.CatID='e'
    ORDER BY one.Category;


    ---------------------


    SELECT one.ParentID
    , one.Category AS Parent
    , one.CatID
    , one.CategoryID
    , two.Category
    , two.CategoryID
    , three.Category
    , three.CategoryID
    FROM (
    categories AS one
    INNER
    JOIN categories AS two
    ON two.ParentID = one.CategoryID
    )
    INNER
    JOIN categories AS three
    ON three.ParentID = two.CategoryID
    WHERE one.ParentID = 0
    AND one.CatID = 'e'
    ORDER
    BY one.Category
    , two.Category
    , three.Category

  15. #15
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    use the second query and change the INNER JOINs to LEFT OUTER JOINs

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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