SQL Issue with Join
Hello experts. I have attached a document for illustration as well. You can see in the attachment my first query with multiple joins is working correctly and returning two test records per the parameters I gave it. The last table I want to join is a table called stock_quant. Below is the sql which returns two records that you can also see in my attachment. The problem I am having is when I attempt to join this last table instead of the two records getting added to my original two records, I get a ton of duplicated data. Hoping someone can take a look at give me the proper syntax in my join as to what I am missing. Thanks ahead of time from a newbietest_example.docxtest_example.docx
select * from stock_quant
where product_id = '15448'
and location_id in ('16', '28', 99)test_example.docx
select * from stock_quant
where product_id = '15448'
and location_id in ('16', '28', 99)test_example.docx
ASKER
I did provide screen shots that were uploaded. I'm a newbie at this so not 100% what else I am suppose to do. As far as a table storing quantities, that is from our erp vendor so It's not by my choice. Not sure what else I am suppose to provide other that what I attached in my document.
Not everyone is keen in opening a potential harmful container format when it is not necessary. Please just embed the content in your post directly using the code and image tags.
ASKER
Ok see below. First code works correct
select product_product.id, product_product.default_code, product_product.variant_name, sale_order_line.product_id, sale_order.id,
sale_order.name, sale_order.state,
sale_order_line.id as solid, sale_order_line.product_uom_qty, stock_warehouse.id, stock_warehouse.code,
stock_warehouse.lot_stock_id, sale_order_line.state as saleorderstate
from sale_order
join sale_order_line on sale_order.id=sale_order_line.order_id
join product_product on sale_order_line.product_id=product_product.id
join stock_warehouse on sale_order_line.location_id=stock_warehouse.id
where stock_warehouse.lot_stock_id in ('16', '28', '99')
and product_product.id = '15448'
and sale_order.state = 'reserved'
Second code Query to show last table I need to join
select * from stock_quant
where product_id = '15448'
and location_id in ('16', '28', 99)
Query to add last table
select product_product.id, product_product.default_code, product_product.variant_name, sale_order_line.product_id, sale_order.id,
sale_order.name, sale_order.state,
sale_order_line.id as solid, sale_order_line.product_uom_qty, stock_warehouse.id, stock_warehouse.code,
stock_warehouse.lot_stock_id, stock_quant.location_id, sale_order_line.state as saleorderstate
from sale_order
join sale_order_line on sale_order.id=sale_order_line.order_id
join product_product on sale_order_line.product_id=product_product.id
join stock_warehouse on sale_order_line.location_id=stock_warehouse.id
join stock_quant on stock_warehouse.lot_stock_id = stock_quant.location_id
where stock_warehouse.lot_stock_id in ('16', '28', '99')
and product_product.id = '15448'
and sale_order.state = 'reserved'
Duplicating records
select product_product.id, product_product.default_code, product_product.variant_name, sale_order_line.product_id, sale_order.id,
sale_order.name, sale_order.state,
sale_order_line.id as solid, sale_order_line.product_uom_qty, stock_warehouse.id, stock_warehouse.code,
stock_warehouse.lot_stock_id, sale_order_line.state as saleorderstate
from sale_order
join sale_order_line on sale_order.id=sale_order_line.order_id
join product_product on sale_order_line.product_id=product_product.id
join stock_warehouse on sale_order_line.location_id=stock_warehouse.id
where stock_warehouse.lot_stock_id in ('16', '28', '99')
and product_product.id = '15448'
and sale_order.state = 'reserved'
Second code Query to show last table I need to joinselect * from stock_quant
where product_id = '15448'
and location_id in ('16', '28', 99)
Query to add last table
select product_product.id, product_product.default_code, product_product.variant_name, sale_order_line.product_id, sale_order.id,
sale_order.name, sale_order.state,
sale_order_line.id as solid, sale_order_line.product_uom_qty, stock_warehouse.id, stock_warehouse.code,
stock_warehouse.lot_stock_id, stock_quant.location_id, sale_order_line.state as saleorderstate
from sale_order
join sale_order_line on sale_order.id=sale_order_line.order_id
join product_product on sale_order_line.product_id=product_product.id
join stock_warehouse on sale_order_line.location_id=stock_warehouse.id
join stock_quant on stock_warehouse.lot_stock_id = stock_quant.location_id
where stock_warehouse.lot_stock_id in ('16', '28', '99')
and product_product.id = '15448'
and sale_order.state = 'reserved'
Duplicating records
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Here is a screen shot of the some more of the results fields for a specific sales order. Looks like duplicates to me as the actual sales order for this test product number (see second screen shot) is on contained on one line so I apologize as I'm not sure I understand by when you so there are no duplicates. So I guess my question is how do I achieve the correct result I am looking for?
Your SQL statement at the end of the post raises some serious questions.
1) Check your data types. ID values are normally integer, not string, thus using quotes is not necessary. It creates an query compilation overhead for the necessary type casts.
2) Don't mix data types in the literal list of the IN predicate. Either location_id is integer, then all values must be integer or it is string, then all values must be quoted.
3) A table for storing only quantities sounds like a flawed data model as you modeled the relation as an attribute.