SitePoint Sponsor

User Tag List

Page 2 of 2 FirstFirst 12
Results 26 to 29 of 29
  1. #26
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    okay, you already have two WHERE clauses in there, one for each of the subqueries in your UNION

    however, the specific request that you mentioned -- "just the apple row" -- takes a bit of finagling, if you're trying to put your query together in a scripting language

    what you have to do is add this --
    Code:
    AND subcat.category_name = 'Apple'
    to the second subquery's WHERE clause, and, as well, discard the entire first subquery of the UNION, because its job is to return the categories only, and 'Apple' is not a category, it's a subcategory
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  2. #27
    SitePoint Zealot daveporter's Avatar
    Join Date
    Mar 2002
    Location
    Perth, Western Australia
    Posts
    133
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi everyone,

    I'm just looking at the sql discussed here & the one thing I don't quite understand is the use of:

    cat.whatever and subcat.whatever in the select statement below. cat is not a table name or a field in the table, just wondering where it comes from ?

    TIA - Dave Porter


    select cat.categoryID
    , cat.category_name
    , null as subcategory_name
    from categories as cat
    where cat.parentID = 0
    union all
    select subcat.categoryID
    , cat.category_name
    , subcat.category_name as subcategory_name
    from categories as cat
    inner
    join categories as subcat
    on cat.categoryID = subcat.parentID
    where cat.parentID = 0
    order
    by 2,3

  3. #28
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by daveporter
    cat.whatever and subcat.whatever in the select statement below. cat is not a table name or a field in the table, just wondering where it comes from ?
    those are table aliases

    have another read of the sql, and be on the lookout for the word "as"

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #29
    dooby dooby doo silver trophybronze trophy
    spikeZ's Avatar
    Join Date
    Aug 2004
    Location
    Manchester UK
    Posts
    13,804
    Mentioned
    157 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by nacho
    I would love to hear why do you advice him/her to do so though.
    If I'm not mistaken ENUM is MySQL specific and if you were to migrate at some point to another database system eg Access it would need to be re-assigned where as CHAR wouldn't.
    Mike Swiffin - Community Team Advisor
    Only a woman can read between the lines of a one word answer.....


Bookmarks

Posting Permissions

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