asked on

SQL Query Join

would like to add another join to an already existing joined query.

The current query is (and works so far) is:

SELECT t.intProductID AS intProductID FROM tblProductConfiguration AS t LEFT OUTER JOIN tblProductVariants AS tt ON t.intVariantID = tt.ID AND t.intProductID = tt.intProductID WHERE (tt.txtVariant = 'Colour') AND (t.txtConfigurationName = 'White')

This query results in a data set of:
intProductID
33
157
320
321
720
740
754
755
756
757

The problem i need to resolve is that whilst a product ID can be returned in a dataset like above, i then need to check is that product ID has a flag set against it in another table of the database.

The table is called "tblProducts" and has two fields that need to equal "1", otherwise they should not be in the list, the fields are:

intActiveProduct
intWebProduct

So in summary, i need to add the logic:

"WHERE tblProducts.intActiveProduct = 1 AND tblProducts.intWebProduct = 1" to the query.

Mlanda T

SELECT t.intProductID AS intProductID 
FROM tblProductConfiguration AS t 
	INNER JOIN tblProducts p ON p.intProductID = t.intProductID AND p.intActiveProduct = 1 AND p.intWebProduct = 1
	LEFT OUTER JOIN tblProductVariants AS tt ON t.intVariantID = tt.ID AND t.intProductID = tt.intProductID 
WHERE (tt.txtVariant = 'Colour') AND (t.txtConfigurationName = 'White')

Open in new window

ASKER CERTIFIED SOLUTION

Vitor Montalvão

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Chris Luttrell

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Vitor Montalvão

@Chris, that should be only true if the filter were on the columns in the JOIN statement, so tt.ID or tt.intProductID.

Mlanda T

@Victor, am not sure with your assertion though. IMHO, I think @Crhis's assertion remains correct right, once you include ANY column from the LEFT OUTER JOIN table in the WHERE clause (and your filter is such that it does not allows NULLs from these filtered columns), you are essentially making it an INNER JOIN, even if the columns are NOT in the JOIN statement...

Vitor Montalvão

If you have a copy of AdventureWorks database just run the following:

SELECT *
FROM Production.Product P
	INNER JOIN Production.ProductModel M ON P.ProductModelID=M.ProductModelID
WHERE P.Color='Black'

SELECT *
FROM Production.Product P
	LEFT OUTER JOIN Production.ProductModel M ON P.ProductModelID=M.ProductModelID
WHERE P.Color='Black'

Open in new window

For what you're saying they should return the same rows, right? But the first one returns 89 and second one 93. It would also return 89 rows if you filter out the NULL values from ProductModelID:

SELECT *
FROM Production.Product P
	LEFT OUTER JOIN Production.ProductModel M ON P.ProductModelID=M.ProductModelID
WHERE P.Color='Black' AND P.ProductModelID IS NOT NULL

Open in new window

Chris Luttrell

@Vitor, your WHERE clause only referenced your primary (LEFT) table. Try the same thing referencing a column in the ProductModel table. I know my SQL constructs and don't make assumptions on that.
@MlandaT, thanks for backing me up.

Vitor Montalvão

Ok, Chris, got it. I did the test by filtering by a column from the right table and got exactly the same rows as an INNER JOIN. I think my mind was set only to think from the left side :)
Cheers.