-
May 29th, 2022, 05:36 AM
#1
Thread Starter
Addicted Member
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
-
May 29th, 2022, 09:31 PM
#2
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:
SELECT ...
FROM products p
INNER JOIN servingsparts sp ON sp.productid = p.id
INNER JOIN servings s ON s.id = sp.servingid
WHERE p.id = @productid
-
May 30th, 2022, 02:17 AM
#3
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
-
May 30th, 2022, 02:53 AM
#4
Re: Can't quite figure out how to left join two other tables and get data from them
Originally Posted by Zvoni
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>
-
May 30th, 2022, 03:17 AM
#5
Re: Can't quite figure out how to left join two other tables and get data from them
Originally Posted by wqweto
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
-
May 30th, 2022, 11:57 PM
#6
Thread Starter
Addicted Member
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.
-
May 30th, 2022, 11:58 PM
#7
Thread Starter
Addicted Member
Re: Can't quite figure out how to left join two other tables and get data from them
Nope, close though-snack bar
-
May 31st, 2022, 12:43 AM
#8
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.
-
May 31st, 2022, 12:52 AM
#9
Re: Can't quite figure out how to left join two other tables and get data from them
Originally Posted by pmeloy
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.
Originally Posted by pmeloy
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
-
May 31st, 2022, 03:34 AM
#10
Thread Starter
Addicted Member
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
-
May 31st, 2022, 03:50 AM
#11
Re: Can't quite figure out how to left join two other tables and get data from them
OK, I've clearly misinterpreted things.
-
May 31st, 2022, 03:53 AM
#12
Thread Starter
Addicted Member
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
-
May 31st, 2022, 04:07 AM
#13
Re: Can't quite figure out how to left join two other tables and get data from them
Originally Posted by jmcilhinney
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
-
May 31st, 2022, 04:15 AM
#14
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
-
May 31st, 2022, 04:19 AM
#15
Thread Starter
Addicted Member
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.
-
May 31st, 2022, 04:20 AM
#16
Re: Can't quite figure out how to left join two other tables and get data from them
Originally Posted by pmeloy
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
-
May 31st, 2022, 04:25 AM
#17
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>
-
May 31st, 2022, 04:26 AM
#18
Thread Starter
Addicted Member
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
-
May 31st, 2022, 04:29 AM
#19
Re: Can't quite figure out how to left join two other tables and get data from them
Originally Posted by wqweto
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
-
May 31st, 2022, 04:30 AM
#20
Thread Starter
Addicted Member
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.
-
May 31st, 2022, 04:34 AM
#21
Re: Can't quite figure out how to left join two other tables and get data from them
Originally Posted by pmeloy
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
-
May 31st, 2022, 04:52 AM
#22
Thread Starter
Addicted Member
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
-
May 31st, 2022, 04:55 AM
#23
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
-
May 31st, 2022, 05:15 AM
#24
Re: Can't quite figure out how to left join two other tables and get data from them
Originally Posted by pmeloy
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>
-
May 31st, 2022, 05:35 AM
#25
Thread Starter
Addicted Member
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
-
May 31st, 2022, 05:37 AM
#26
Thread Starter
Addicted Member
Re: Can't quite figure out how to left join two other tables and get data from them
Originally Posted by wqweto
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.
-
May 31st, 2022, 06:20 AM
#27
Thread Starter
Addicted Member
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).
-
May 31st, 2022, 06:24 AM
#28
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|