July 29, 2011 at 3:30 am
Hi,
A question about working with joins.
My query looks like:
SELECT dbo.Product.Id, dbo.ProductStock.AvgCostPrice
FROM dbo.Product INNER JOIN
dbo.ProductStock ON dbo.Product.Id = dbo.ProductStock.ProductId
WHERE (dbo.Product.Family = '0001') AND (dbo.Product.Number = '043001') AND (dbo.ProductStock.BranchId = 13)
My dbo.ProductStock table does not have a result for BranchId 13.
But is till wanne have the dbo.Product.Id as result (dbo.ProductStock.AvgCostPrice
can and will be NULL then).
How to do this?
I have seen it before but i can't remember the trick we used.
Kind regards,
July 29, 2011 at 3:39 am
July 29, 2011 at 3:42 am
Thats not the answer i'm looking for.
The BranchId will be in the query.
July 29, 2011 at 3:52 am
July 29, 2011 at 4:04 am
SELECT dbo.Product.Id, dbo.ProductStock.AvgCostPrice
FROM dbo.Product
LEFT OUTER JOIN dbo.ProductStock ON dbo.Product.Id = dbo.ProductStock.ProductId
WHERE dbo.Product.Family = '0001' AND dbo.Product.Number = '043001' AND (dbo.ProductStock.BranchId = 13)
This will never give a result because there is no row in dbo.ProductStock with BranchId=13.
But i still wanne have my ProductId. The left outer join alone doesn't do the trick.
I need to alow that there will be no result in the dbo.ProductStock and still give the ProductId from the Product table.
July 29, 2011 at 4:20 am
SELECT dbo.Product.Id, dbo.ProductStock.AvgCostPrice
FROM dbo.Product
LEFT OUTER JOIN dbo.ProductStock ON dbo.Product.Id = dbo.ProductStock.ProductId
WHERE dbo.Product.Family = '0001' AND dbo.Product.Number = '043001'
Allow me to explain my earlier query , I would also strongly suggested you lookup JOINS in MSDn
if there is no value for branch id = 13 then youwill never get results for it when performing a inner join
Instead , if you want results regardless of branchid = 13 being present or not in the productstock table then you need to make sure you use a LEFT outer join to productstock table so it it returns all data from the product table and any matching data from the product stock table.
However this will still not work until you have removed the filter for branchid = 13 from the query as this lets sql to filter the result set only for those records where bracnhid = 13 and since you dont have any it would behave the same way as an inner join
July 29, 2011 at 5:24 am
This is what i already know. I understand how joins work. Thats not the issue.
Still i know there is a way to get the productId even there is no result in the ProductStock table.
Thnx for replaying anyway 🙂
July 30, 2011 at 9:21 pm
Your query has BranchId as part of the where clause which eliminates resultset as a whole where BranchId is not 13. You have to include BranchId in your join to get the result that you want. and use left / right outer join to get appropriate result.
August 1, 2011 at 8:55 am
If I understand what you want correctly, you need to make the BranchId filter a part of the outer join.
SELECT
dbo.Product.Id,
dbo.ProductStock.AvgCostPrice
FROM
dbo.Product
LEFT OUTER JOIN dbo.ProductStock
ON dbo.Product.Id = dbo.ProductStock.ProductId
AND dbo.ProductStock.BranchId = 13
WHERE
dbo.Product.Family = '0001'
AND dbo.Product.Number = '043001'
August 1, 2011 at 9:02 am
YES!
Thats it :).
I knew it was pretty easy but i totally forgot how to do it.
Thanks alot
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply