Results 1 to 3 of 3

Thread: more query help

  1. #1
    Join Date
    Jun 2010
    Posts
    18

    Unanswered: more query help

    I have a table of people
    I have a table of items
    I have a table of inventory (this table tells which person has how many of each product, but does not have a record if the person has "0")

    people.name
    --------------
    jay
    mark
    lisa

    products.item
    ---------------
    item1
    item2
    item3
    item4

    inventory.person,item,qty (again, this table does not have a row if the person has 0 balance)
    -----------------------------
    jay - item1 - 2
    jay - item2 - 1
    mark - item3 - 1
    mark - item1 - 1
    lisa item3 - 3

    I need a query that will give me a dataset that lists all the items from the item table, and all the people from people table, regardless of whether they have the item or not - just showing a "0" for the balance, thus:

    jay - item1 - 2
    jay - item2 - 1
    jay - item3 - 0
    jay - item4 - 0

    mark - item1 - 1
    mark - item2 - 0
    mark - item3 - 1
    mark - item4 - 0

    lisa item1 - 0
    lisa item2 - 0
    lisa item3 - 3
    lisa item4 - 0


    I can't think of the type of join this would be, i'm only familiar with inner/right joins, and outer/left joins... thank you!!

  2. #2
    Join Date
    Jun 2010
    Posts
    18
    oh, I think I need to do a cartesian join (cross join..) i'm going to work through it, but if someone can confirm this is correct...

  3. #3
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    You need:
    - a cartesian join between people and products
    - an outer join with inventory
    - NVL() function
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

Tags for this Thread

Posting Permissions

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