Hi, how can I get another inner join in here to improve the performance of this query? I'm querying a training courses database. The query below works fine but uses a sub query and an inner join, I read that sub queries are not great for large datasets so would like to use another inner join to get the data from the other table. Any ideas? SELECT Product.ProductId, ProductName, Duration, Discounted, (SELECT QualificationName FROM dbo.ProductQualification WHERE dbo.ProductQualification.QualificationId = dbo.Product.QualificationId) AS Qualification FROM dbo.Product INNER JOIN dbo.ProductCategory_Junction ON Product.ProductId = dbo.ProductCategory_Junction.ProductId WHERE dbo.ProductCategory_Junction.CategoryId = 2 Any idea how I could replace the sub query with an inner join? Or is it just not worth it..?
I might be missing something but aer you looking after this? SELECT Product.ProductId, ProductName, Duration, Discounted, dbo.ProductQualification.QualificationName AS Qualification FROM dbo.Product JOIN dbo.ProductQualification on dbo.ProductQualification.QualificationId = dbo.Product.QualificationId JOIN dbo.ProductCategory_Junction ON Product.ProductId = dbo.ProductCategory_Junction.ProductId WHERE dbo.ProductCategory_Junction.CategoryId = 2
I would go for a left outer join, unless every single product will have a corresponding entry in the ProductQualification table [] SELECT Product.ProductId , ProductName , Duration , Discounted , ProductQualification.QualificationName AS Qualification FROM dbo.Product INNER JOIN dbo.ProductCategory_Junction ON Product.ProductId = dbo.ProductCategory_Junction.ProductId LEFT OUTER JOIN dbo.ProductQualification ON dbo.ProductQualification.QualificationId = dbo.Product.QualificationId WHERE dbo.ProductCategory_Junction.CategoryId = 2