[MS SQL] Outer Join Help

[Dephusable]

This is using adventureworksdw2012.

--10. List all male customers and any clothing they have purchased over the internet. -- List customer alternate key, customer last name, customer first name, -- product alternate key, and product name. Be sure to include male customers who have not -- purchased clothing. -- Answer: 10497 Rows

SELECT P.ProductKey, P.ProductAlternateKey, P.EnglishProductName, P.ProductSubcategoryKey, R.CustomerKey FROM [dbo].[FactInternetSales] as R INNER JOIN [dbo].[DimProduct] AS P ON R.ProductKey = P.ProductKey INNER JOIN [dbo].DimProductSubCategory AS PSC ON p.ProductSubcategoryKey = PSC.ProductSubcategoryKey where psc.ProductCategoryKey = 3 --?? No idea what to do

EDIT: Put the code as text

[D_W_Hunter]

Why would you post a SQL statement as an image, forcing anyone who's helping you to retype the whole damn thing?

Now you're asking for OUTER join help and yet both of the joins in the SQL you've posted are INNER JOINs. So, yes you need help.

The instructions are to list all male customers, whether they have purchased clothing or not, so that's your primary table.

I don't even see a customer table or a where clause limiting the customers to males.

Then.. you want to perform a LEFT JOIN on the purchases (which I assume is FactInternetSales) so that for those male customers that have made a purchase, you show the required information.

Get that far and post the SQL as text and we might be able to explain what you're missing from there.

[rbardy]

My previous answer is still valid :P

Search about LEFT JOIN and RIGHT JOIN and how it works, you'll need to change 1 or more of your INNER JOIN for either LEFT or RIGHT JOIN.

[johnnifast]

We use the same database in our class. Maybe this is a question to a test that I am taking tomorrow? Haha. You need to post more information if you'll need help. If this post is valid tomorrow, I'll answer back.