Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2013
    Posts
    25

    Unanswered: SQL Syntax question, with 2 example questions

    In the following code:

    select name,
    (select SUM(quantitysold) from tblproductsales
    where tblProducts.id = productId)
    from tblproducts
    order by name

    Why isn't it necessary to qualify productId? I know it comes from tblproductsales.

    Here is my 2nd question:

    Here are 2 code snippets with the original table code:


    Comment: I much prefer the 1st snippet of code because it tells me the id for the product not sold by putting a 1 in the name column. Normally I would think that you do, select col1, col2 .....
    So the syntax with ID name was confusing....? its like col1 col2 with no comma? Could someone please shed some light on what is happening and what other syntax rules like this might be lurking?

    Andrew

    create table tblproducts
    (
    id int,
    name nvarchar(20),
    description nvarchar(50)
    );

    create table tblproductsales
    (
    id int,
    productid int,
    unitprice numeric,
    quantitysold int
    );



    -- non correlated SQ where this item was not ever sold

    select ID name, description
    from tblproducts
    where ID NOT IN (select distinct productid from tblProductsales)

    Results:

    ------------------------------
    name description
    1 52 inch tv
    ------------------------------

    -- non correlated SQ where these items have been sold

    select name, description
    from tblproducts
    where ID IN (select distinct productid from tblProductsales)


    Results:

    ------------------------------
    name description
    tv 52 inch tv
    ------------------------------

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    So the syntax with ID name was confusing....? its like col1 col2 with no comma?
    "name"(or "col2") may be interpreted as an alias for a column "ID"(or "col1") in the SELECT clause.

  3. #3
    Join Date
    Mar 2013
    Posts
    25

    Thank you

    Thank you.
    Andrew

  4. #4
    Join Date
    Dec 2008
    Location
    At work...
    Posts
    92
    Quote Originally Posted by northstar999 View Post
    In the following code:
    select name,
    (select SUM(quantitysold) from tblproductsales
    where tblProducts.id = productId)
    from tblproducts
    order by name

    Why isn't it necessary to qualify productId? I know it comes from tblproductsales.
    You don't need to qualify productId since that's the only one column with that name in your sub-query.

    In your sub-query you can even write ...where tblProducts.Id = Id), where the unqualified Id belongs to the subquery's table (tblproductsales).

    And you can also write ...where name = 'sometext'), where the unqualified name belongs to the main query's table (tblproducts).

    I.e. first search the sub-query's tables for the Id column, if not found step out from the sub-query and search that "levels" tables etc.

  5. #5
    Join Date
    Mar 2013
    Posts
    25

    Trying things out

    I tried out what I think you were trying to say? In the subquery I could change, "productId = tblProducts.id" to this, "tblProducts.Id = Id" in #2 but that didn't work because the results changed.

    But no worries because I am learning something by playing around and trying things. I sure appreciate your post. Thank you

    Andrew

    #1
    select name,
    (select SUM(quantitysold) from tblproductsales
    where productId = tblProducts.id) as TotalQuantity
    from tblproducts
    order by name

    #2
    select name,
    (select SUM(quantitysold) from tblproductsales
    where tblProducts.Id = Id) as TotalQuantity
    from tblproducts
    order by name

  6. #6
    Join Date
    Dec 2008
    Location
    At work...
    Posts
    92
    Quote Originally Posted by northstar999 View Post
    I tried out what I think you were trying to say? In the subquery I could change, "productId = tblProducts.id" to this, "tblProducts.Id = Id" in #2 but that didn't work because the results changed.
    Sorry, I didn't mean to confuse you. My tblProducts.Id = Id example was just meant to describe scope rules, not to give you an alternative solution.

    Playing around and trying things is a necessary to learn SQL! Good luck!!!

  7. #7
    Join Date
    Mar 2013
    Posts
    25

    Thank you

    Yes, no worries. You did fine, thank you for replying
    Andrew

Posting Permissions

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