Results 1 to 28 of 28

Thread: Can't quite figure out how to left join two other tables and get data from them

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    Dec 2012
    Posts
    136

    Can't quite figure out how to left join two other tables and get data from them

    I have three tables. One is a product which I need the id and name from. The second is a servings table that has only two fields, id and name. the third is a servingsparts table with several columns.

    products and servings are one to one but servings and servingsparts are one to many products has a servingid field that points to the serving table while the serving table points to nothing. The servingsparts table has a servingid field that points at the servings table.

    I am trying to get all the servingsparts associated with the selected product
    This is what I have so far:It does get the servingsparts data but nothing from servings or products, those come back blank
    Code:
    select p.name,s.name,sp.name,sp.id as partid,sp.price as partprice,sp.amount as serving 
    from (servingsparts sp
    LEFT JOIN servings s on s.id=sp.servingid)
    left join products p on p.servingid=s.id
    WHERE p.id=@id
    ORDER BY p.name
    I know the brackets are necessary for accdb files but I don't quite understand why

    OOps, forgot to show the results
    Name:  queryresult.jpg
Views: 170
Size:  11.0 KB

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Can't quite figure out how to left join two other tables and get data from them

    Why are you trying to do a left join in the first place? Is there a specific reason because it doesn't seem appropriate. A left join is used when you want all records from the table on the left, even if there is no corresponding record in the table on the right. Can there be a servingsparts record with no servings record? Can there be a servingsparts record with no products record? I'm guessing not, so left joins don't make sense. You can probably have a products record with no servingsparts record, so left join could make sense there, but you're not trying to get all products records. You should be using inner joins.
    sql Code:
    1. SELECT ...
    2. FROM products p
    3. INNER JOIN servingsparts sp ON sp.productid = p.id
    4. INNER JOIN servings s ON s.id = sp.servingid
    5. WHERE p.id = @productid

  3. #3
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: Can't quite figure out how to left join two other tables and get data from them

    If i read this correctly, "servings" is the Master-Table, and "products" and "servingsparts" are child-tables to servings.
    It doesn't make sense to put child-tables left into a LEFT join.
    Rule of Thumb: For LEFT JOINS the Master-Table goes to the left, child-tables to the right

    and JMC is right: Use INNER JOINS
    Code:
    SELECT ...... 
    FROM servings s //Master-Table
    INNER JOIN products p ON p.servingsid=s.ID //Child-Table
    INNER JOIN servingspart sp ON sp.servingid=s.ID  //Child-Table
    WHERE p.ID=@ProductID
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  4. #4
    PowerPoster wqweto's Avatar
    Join Date
    May 2011
    Location
    Sofia, Bulgaria
    Posts
    5,120

    Re: Can't quite figure out how to left join two other tables and get data from them

    Quote Originally Posted by Zvoni View Post
    If i read this correctly, "servings" is the Master-Table, and "products" and "servingsparts" are child-tables to servings.
    OP clearly says "products and servings are one to one" which doesn't sound like master-detail relation.

    Also says "servings and servingsparts are one to many" which makes sense.

    Also "products has a servingid field that points to the serving table" which is normal and then "serving table points to nothing" which is wrong on many levels (tables don't "point") so best to ignore for now.

    Also "servingsparts table has a servingid field that points at the servings table" which is normal.

    It seems original query (although using unnecessary outer joins) must return correct results.

    Either OP's table/FK understanding is not correct or the actual data in these tables is wrong.

    cheers,
    </wqw>

  5. #5
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: Can't quite figure out how to left join two other tables and get data from them

    Quote Originally Posted by wqweto View Post
    OP clearly says "products and servings are one to one" which doesn't sound like master-detail relation.

    Also says "servings and servingsparts are one to many" which makes sense.

    Also "products has a servingid field that points to the serving table" which is normal and then "serving table points to nothing" which is wrong on many levels (tables don't "point") so best to ignore for now.

    Also "servingsparts table has a servingid field that points at the servings table" which is normal.

    It seems original query (although using unnecessary outer joins) must return correct results.

    Either OP's table/FK understanding is not correct or the actual data in these tables is wrong.

    cheers,
    </wqw>
    Look at OP's SQL in Line 4
    select p.name,s.name,sp.name,sp.id as partid,sp.price as partprice,sp.amount as serving
    from (servingsparts sp
    LEFT JOIN servings s on s.id=sp.servingid)
    left join products p on p.servingid=s.id

    WHERE p.id=@id
    ORDER BY p.name
    Products has a "servingid"-Field --> Child-Table to "serving"

    but after reading the first post again: What does OP mean with "products and servings are one to one"??

    Maybe if OP describes his "real life" scenario, because right now, i have something of a restaurant in my imagination
    Last edited by Zvoni; May 30th, 2022 at 03:23 AM.
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  6. #6

    Thread Starter
    Addicted Member
    Join Date
    Dec 2012
    Posts
    136

    Re: Can't quite figure out how to left join two other tables and get data from them

    Sorry I messed up when I said the products to servings were one to one. I was thinking that because one product can have only one servingid. In fact is is many to one so I guess the servings table is the master? Many products can point to one serving and servingparts can only point to one serving so many to one and one to many.

    I think of the products table as the master because that is the one I am concerned about and actually use.

  7. #7

    Thread Starter
    Addicted Member
    Join Date
    Dec 2012
    Posts
    136

    Re: Can't quite figure out how to left join two other tables and get data from them

    Nope, close though-snack bar

  8. #8
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Can't quite figure out how to left join two other tables and get data from them

    There should be a many-many relationship between Product and Serving, i.e. one product can be included in many servings and each serving can include many products. That relationship is achieved via ServingPart, which presumably relates to one product in one serving. Neither Product nor Serving know about the other. ServingPart has a ServingId and a ProductId. Those are the relationships.

  9. #9
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: Can't quite figure out how to left join two other tables and get data from them

    Quote Originally Posted by pmeloy View Post
    Sorry I messed up when I said the products to servings were one to one. I was thinking that because one product can have only one servingid. In fact is is many to one so I guess the servings table is the master? Many products can point to one serving and servingparts can only point to one serving so many to one and one to many.

    I think of the products table as the master because that is the one I am concerned about and actually use.
    Quote Originally Posted by pmeloy View Post
    Nope, close though-snack bar
    Nope.
    You have to think the other way round:
    A customer enters the snack-bar
    He places his (one) order (which you call "serving")
    He get's his "serving", consisting (!!) of:
    Product "coffee"
    Product "cherry-pie"
    Serving-part "coffee-cup"
    serving-part "fork"
    etc...

    bottom line:
    "One" serving can have "many" products --> 1:m-relation
    "one" serving can have "many" servingparts --> 1:m-relation

    BUT:
    I am trying to get all the servingsparts associated with the selected product
    Meaning: A servingpart "fork" doesn't make sense with product "coffee", but it makes sense with product "cherry-pie", "strawberry-pie", "apple-pie" etc.
    Also: A product "coffee" makes sense to have a servingpart "cup", "saucer", "teaspoon" etc.

    If you really want the servingparts associated with a product, you need an additional table in "m:m"-relation

    EDIT: Took me to long to write this post.
    jmc beat me to it
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  10. #10

    Thread Starter
    Addicted Member
    Join Date
    Dec 2012
    Posts
    136

    Re: Can't quite figure out how to left join two other tables and get data from them

    Actually it is probably the naming that is confusing. Servings are a way to break up packaged parts or sell prepared items.

    Example here in the philippines it is very common to buy a single cigarette and somewhat less common to buy a pack. So
    Product Marlboro Red
    Serving "regular cigarettes"
    ServingParts#1 "single",1,8 (WOrd to show, number of cigarettes,cost in piso
    ServingParts#2 "Package",20,160,each
    ServingParts#3 "Carton",200,1550,each

    Another serving would be "Expensive Cigarettes" or "Mango Juice" but Mango Juice would be different
    Serving "Juice"
    ServingParts "Reg Cup",10,10,Oz

    Servingparts keeps the price, amount and units so 10 Oz is converted to ml then used for all calculations in its smallest form

    I guess I should have used a different word than serving but I started with Sellsize so at least it is a little more apropos

    So one product can only know one serving but there are many products some some will have the same servings and others that have different servings Like Marlboro Blue is more expensive so it has a serving of it's own with its own servingparts. Same structure as the regular cigarettes serving but different prices

  11. #11
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Can't quite figure out how to left join two other tables and get data from them

    OK, I've clearly misinterpreted things.

  12. #12

    Thread Starter
    Addicted Member
    Join Date
    Dec 2012
    Posts
    136

    Re: Can't quite figure out how to left join two other tables and get data from them

    Not a reflection on you. I'm a newb basically in all of this so my terminology sucks bigtime

  13. #13
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: Can't quite figure out how to left join two other tables and get data from them

    Quote Originally Posted by jmcilhinney View Post
    OK, I've clearly misinterpreted things.
    That makes two of us
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  14. #14
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: Can't quite figure out how to left join two other tables and get data from them

    After reading through the OP's explanation, i'm starting to think, that the "serving"-table is actually something like a "Category"

    Category (formerly "serving")
    ID --> Primary Key
    Designation --> examples: "regular cigarettes", "expensive cigarettes", "Juice"

    Product
    ID --> Primary Key
    Prod-No. (Optional)
    Designation --> examples: "Marlboro Red", "Marlboro Blue", "Mango Juice"
    Cat_ID --> Foreign Key to Category

    SellSize (formerly "servingparts")
    ID --> Primary Key
    Designation --> examples: "Package", "Carton", "Cup"
    Amount --> examples: 20, 200, 10
    Price --> examples:160, 1550, 10
    Unit --> examples: "each", "each", "oz" --> Or Foreign Key to a "Units"-Master-Table
    Prod_ID --> Foreign Key to Product
    Last edited by Zvoni; May 31st, 2022 at 04:19 AM.
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  15. #15

    Thread Starter
    Addicted Member
    Join Date
    Dec 2012
    Posts
    136

    Re: Can't quite figure out how to left join two other tables and get data from them

    servings and servingparts are still the table names. Category passed though my brain but just didn't seem to fit.

  16. #16
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: Can't quite figure out how to left join two other tables and get data from them

    Quote Originally Posted by pmeloy View Post
    servings and servingparts are still the table names. Category passed though my brain but just didn't seem to fit.
    Read my example above, and tell me again, that "Category" doesn't fit....

    "serving" = "regular cigarettes"
    under this "Category" can be: "Marlboro Red", "Lucky Strike", "West Red"
    meaning: "Marlboro Red" only has one Category ("regular Cigarettes"), but that "Category" can have many products
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  17. #17
    PowerPoster wqweto's Avatar
    Join Date
    May 2011
    Location
    Sofia, Bulgaria
    Posts
    5,120

    Re: Can't quite figure out how to left join two other tables and get data from them

    Either OP's table/FK understanding is not correct or the actual data in these tables is wrong.
    Ok, it turned out it's worse -- the whole schema is bonkers.

    In your schema you have products.servingid so multiple products can have the same "serving" e.g. Marlboro, Camel, etc. Why?

    You cannot "reuse" your "servings" for different products because when you designed to keep the price in "servings parts" this become mightily inconvenient *and* 99% of the time does not reflect reality (so called the business logic of your app)

    When Marlboro and Camel use the same "serving" and the price of Camels change you cannot simply modify it in "servings parts" as this would change the prices of Marlboros "parts".

    This is probably the reason why you later decided to keep products to "servings" in a 1-to-1 relationship, not to mess up prices when these change.

    Post a revised schema if you cannot figure out the query for you original question after revising the tables.

    cheers,
    </wqw>

  18. #18

    Thread Starter
    Addicted Member
    Join Date
    Dec 2012
    Posts
    136

    Re: Can't quite figure out how to left join two other tables and get data from them

    It probably won't be of much help but I added the DB as a datasource so I could get this shot from the designer. A product can only have one serving but a serving can has many servingparts

    Attachment 184968

  19. #19
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: Can't quite figure out how to left join two other tables and get data from them

    Quote Originally Posted by wqweto View Post
    Ok, it turned out it's worse -- the whole schema is bonkers.

    In your schema you have products.servingid so multiple products can have the same "serving" e.g. Marlboro, Camel, etc. Why?

    You cannot "reuse" your "servings" for different products because when you designed to keep the price in "servings parts" this become mightily inconvenient *and* 99% of the time does not reflect reality (so called the business logic of your app)

    When Marlboro and Camel use the same "serving" and the price of Camels change you cannot simply modify it in "servings parts" as this would change the prices of Marlboros "parts".

    This is probably the reason why you later decided to keep products to "servings" in a 1-to-1 relationship, not to mess up prices when these change.

    Post a revised schema if you cannot figure out the query for you original question after revising the tables.

    cheers,
    </wqw>
    wqw, read my post #14
    I think his "serving" is actually a Category.
    And his "servingparts" is a "SellSize" of "Product" (NOT of "serving"/Category!), and there it would make sense to fix the price per SellSize OF product.
    1 cigarette is 8 pisos, 20 ("package") is 160 pisos (20x8 pisos), but "Carton" with 200 is 1550 pisos (NOT 200x8 pisos)
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  20. #20

    Thread Starter
    Addicted Member
    Join Date
    Dec 2012
    Posts
    136

    Re: Can't quite figure out how to left join two other tables and get data from them

    I fail to see the problem. If Camel changes prices then just create a new servings with different prices. If the price was in the product table then I would have to have a different product for single cigarettes, packages, or cartons. Much easier this way as far as I can tell.

    I already have a servings editor so the use can create their own on the fly.

  21. #21
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: Can't quite figure out how to left join two other tables and get data from them

    Quote Originally Posted by pmeloy View Post
    I fail to see the problem. If Camel changes prices then just create a new servings with different prices. If the price was in the product table then I would have to have a different product for single cigarettes, packages, or cartons. Much easier this way as far as I can tell.

    I already have a servings editor so the use can create their own on the fly.
    Wrong!
    If you have non-contigous prices, then the price goes into the "SellSize" (your "servingparts").
    If Camel changes his prices, you just edit the prices in "SellSize"/"servingparts".
    "Marlboro Red" will not be affected by a price-change of "Camel"
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  22. #22

    Thread Starter
    Addicted Member
    Join Date
    Dec 2012
    Posts
    136

    Re: Can't quite figure out how to left join two other tables and get data from them

    If Camel and Marlboro red have the same servings then they have the same prices. Marlboro blue is more expensive so I had to create a serving for it alone.

    Not every product has a serving, if the field is 0 then I just assume serving is 1 piece. This picture is of the product editing screen. Anything with a "1" for a serving has a servingid of 0.
    Attachment 184969

  23. #23
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: Can't quite figure out how to left join two other tables and get data from them

    Well, then....
    if you want to make your life unneccessary complicated, be my guest, but i'm out of here
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  24. #24
    PowerPoster wqweto's Avatar
    Join Date
    May 2011
    Location
    Sofia, Bulgaria
    Posts
    5,120

    Re: Can't quite figure out how to left join two other tables and get data from them

    Quote Originally Posted by pmeloy View Post
    If Camel and Marlboro red have the same servings then they have the same prices. Marlboro blue is more expensive so I had to create a serving for it alone.
    That's what I meant by "mightily inconvenient". What you do is possible but a bit unnecessary complicated.

    Usually in most business systems you have separate entity for price lists. Even if you start your app with a single price list for end clients (no B2B) still might be worth considering. Not mixing product measures definition (so called facets) with prices too.

    Anyway, there is no reason for your OP query to return empty results unless the data in the tables is wrong somehow.

    You might want to start decomposing the join until blank results are returned i.e. first start with this

    Code:
    select p.name
    from products p
    WHERE p.id=@id
    ORDER BY p.name
    then continue with this

    Code:
    select p.name,s.name
    from servings s
    left join products p on p.servingid=s.id
    WHERE p.id=@id
    ORDER BY p.name
    then try your final query.

    Try different products @id.

    In one of these 3 tables there is missing data for your particular @id value.

    cheers,
    </wqw>

  25. #25

    Thread Starter
    Addicted Member
    Join Date
    Dec 2012
    Posts
    136

    Re: Can't quite figure out how to left join two other tables and get data from them

    Ah I finally got what I was looking for. Not useful at all but I really wanted to figure it out
    Code:
    select p.name,s.name,sp.name,sp.servings
    FROM (products p
    INNER JOIN servings s ON p.servingid = s.id)
    INNER JOIN servingsparts sp ON sp.servingid=s.id
    order by p.name
    And the result!Attachment 184970

  26. #26

    Thread Starter
    Addicted Member
    Join Date
    Dec 2012
    Posts
    136

    Re: Can't quite figure out how to left join two other tables and get data from them

    Quote Originally Posted by wqweto View Post
    That's what I meant by "mightily inconvenient". What you do is possible but a bit unnecessary complicated.

    Usually in most business systems you have separate entity for price lists. Even if you start your app with a single price list for end clients (no B2B) still might be worth considering. Not mixing product measures definition (so called facets) with prices too.

    </wqw>
    I'm a newb so I can't even imagine what that separate entity might be. I'm very interested now but that is probably better in it's own thread.

  27. #27

    Thread Starter
    Addicted Member
    Join Date
    Dec 2012
    Posts
    136

    Re: Can't quite figure out how to left join two other tables and get data from them

    Strange, that query works perfectly in MDBviewer using the SQL window but I'm still getting blank fields using vb2017/wpf, strange. I get the results and just display in an autocolumn datagrid so I see everything in the returned table (Not me messing it up).

  28. #28
    PowerPoster wqweto's Avatar
    Join Date
    May 2011
    Location
    Sofia, Bulgaria
    Posts
    5,120

    Re: Can't quite figure out how to left join two other tables and get data from them

    Try returning PKs from (some of) the base tables because grids usually need scrollable cursors.

    cheers,
    </wqw>

Posting Permissions

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



Click Here to Expand Forum to Full Width