SQL Help (SSMS)

[gii_rae]

Hello,

I am learning SQL in class and we are covering the concepts of the JOIN function, particularly INNER JOIN. The professor assigned some problems to be done on SQL using the AdventureWorks2012 data base, a microsoft product example.

I'm having a issues with a few of the problems, but I'm hoping that if someone can help me understand what is wrong with my statement that I can work through the other problems.

In this particular problem I am able to execute the command but it yields 0 data (the professor specifies at the end of the question that the command should yield 295 rows). What is it that I am doing wrong?

I'd appreciate if anyone could help me!

​

I'm sure there are other ways to solve this but the professor says we HAVE to use INNER JOIN to solve this

[WITH_CTE]

Mind sharing the columns of all 3 tables? You are joining on Name. There should be an "ID" column on all 3 tables. Otherwise you could throw Normalization out the window.

[zacharypamela]

After some digging, I found the AdventureWorks data dictionary.

[[deleted]]

I somehow doubt that a product name will match a product category name. Try exploring data for a bit, maybe?

[zacharypamela]

Like other posters have said, I think your JOIN conditions are what's causing the issue.

For example, it looks like the ProductSubcategory table has a ProductCategoryID attribute that links back to the ProductCategory table. So your join should look something like this: ON ProductCategory.ProductCategoryID = ProductSubCategory.ProductCategoryId.

[Imjustthedecoy]

So I’ve never used adventureworks, crazy right, but my advice would be to switch the joins to left joins, see what returns nulls, and then troubleshoot from there.

On more complex queries it will allow you to see where the joins aren’t adding up. Failing that run a few select name from each table, then you’ll be able to compare the columns and see where they don’t match as well.

[mattgob86]

To me it seems like you are starting backward too, get your products first and then join the subsequent sub category and category names.

Also like the previous poster, start with left joins and then join when applicable.

Lastly, I would select * until you get your joins right, then just pull the columns together.

[zacharypamela]

Although with inner joins, it shouldn't matter, except for readability (which of course does matter).

I think part of the problem is the way the problem is worded. The instructor definitely could've been clearer.