SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Enthusiast
    Join Date
    Aug 2001
    Location
    NJ
    Posts
    68
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Up for a challenge? It's tough for me atleast!

    Hello everyone, I have not been here for a while, since I have not had the opportunity with intensely working on a project for a LONG time.

    Anyway, the long break has led to some rusty-ness on my behalf i guess, because I cannot figure out a solution to this problem:

    I have 4 tables:
    attributes
    products
    inventory
    attributes_to_products

    I want to get attributes.id, attributes.name, products.retail, products.cost, products.shipdays, and ***inventory.quantity*** from these tables.

    The product table has a column called item_number
    The inventory table has a column called item_number

    If the product is in inventory there is a row added to the inventory table with the products item_number and the quantity that we have on-hand

    However, if we don't have any of the product on hand. there is no entry in the inventory table.

    What I want is for inventory.quantity to show me the quantity on hand if we have it, or give me a zero if not

    The qay my query is written right now, i don't get a result if the item is not on hand.

    Here is my query:

    SELECT
    attributes.id, attributes.name, products.item_number, products.retail, products.cost, products.shipdays, inventory.quantity
    FROM
    attributes, products, attributes_to_products, inventory
    WHERE
    (attributes.pdid = 1) AND (attributes_to_products.attribute_id = attributes.id) AND (products.id = attributes_to_products.product_id) AND (inventory.item_number = products.item_number)


    I know this may be somewhat hard to follow, so if there are any questions please just let me know! Thanks in advance for your help!


    P.S. I am using SQL Server 2000
    signature

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    select attributes.id
         , attributes.name
         , products.item_number
         , products.retail
         , products.cost
         , products.shipdays
         , coalesce(inventory.quantity,0) as inventoryquantity
      from attributes
    inner
      join attributes_to_products
        on attributes.id
         = attributes_to_products.attribute_id
    inner
      join products
        on attributes_to_products.product_id
         = products.id
    left outer
      join inventory
        on products.item_number
         = inventory.item_number
     where attributes.pdid = 1
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Aug 2001
    Location
    NJ
    Posts
    68
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    How long did that take you like 30 seconds?!

    I am impressed, and VERY appreciative.

    Just one question, can you explain the coalesce function that you used?

    Thanks Rudy!
    -Jon
    signature

  4. #4
    <? james('rules'); ?>
    Join Date
    Jun 2004
    Location
    Wales, UK
    Posts
    788
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    He is an SQL consultant, what can you say .
    As for:
    coalesce()
    Go to mysql.com and search for coalesce and it wil bring some handy results up .

    My defination:

    The coalesce function returns the first non-null expression in the list and if all expressions evaluate to null, then the coalesce function will return null.
    Basically, it goes to the first place in the list where inventory.quantity is NOT null, starting from 0.
    Very advanced MySQL.
    If knowledge is power - Why isn't our army librarians?!
    Statistics show that 63% of all statistics are fake.
    When i was little i broke my neck, and i havent looked back since .
    I completed the internet in 1 week. The end boss was pretty easy though .

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    yes, it took approximately 17 years and 30 seconds

    17 years to learn sql, and 30 seconds to format your query

    coalesce is not exactly advanced

    and it's perfect for outer joins

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


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
  •