I have a question in sql server with joins and subqueries

And the question is...

Part 1: Inner query
Write a SELECT statement that retrieves the minimum standard cost for products
grouped in colour as Blue, Yellow and Black. Make sure the standard cost is above 0.0.

Part 2: Outer query
Write a SELECT statement that retrieves the product name, list price, colour from the
product table and minimum standard cost (which will come from the inner query of part
1).
Now add an inner join statement that has the part 1 query as the table as the first part of
the expression. Make sure this is in brackets. Give this statement and alias, such as msc.
For the ON part of the join statement, make sure the colour from the inner query is the
same as the colour from the outer query AND the minimum standard cost of the msc
table, is the same as the standard cost of the product.

In part 1 the query that I have tried is...

SELECT Color, MIN(StandardCost) AS [MinimumStandardCost] FROM SalesLT.Product
WHERE Color='Blue' OR Color='Black' OR Color='Yellow'
GROUP BY Color

And it is showing me the correct answer.

In part 2 my query upto the outer query is....

SELECT Name,ListPrice,Color,StandardCost FROM SalesLT.Product
WHERE StandardCost IN(SELECT MIN(StandardCost) AS [MinimumStandardCost] FROM SalesLT.Product
WHERE Color='Blue' OR Color='Black' OR Color='Yellow'
GROUP BY Color)

a. So the problem arising in this part is when I'm using Group BY it is showing me this table....

       

Name                                  ListPrice       Color      StandardCost
Sport-100 Helmet, Red                   34.99          Red         13.08632    
Sport-100 Helmet, Black                 34.99          Black       13.08633    
Sport-100 Helmet, Blue                  34.99          Blue        13.08634    
Half-Finger Gloves, S                   24.49          Black       9.15935    
Half-Finger Gloves, M                   24.49          Black       9.15936    
Half-Finger Gloves, L                   24.49          Black       9.15937  
Short-Sleeve Classic Jersey, S          53.99          Yellow      41.57238   
Short-Sleeve Classic Jersey, M          53.99          Yellow      41.57239   
Short-Sleeve Classic Jersey, L          53.99          Yellow      41.572310    
Short-Sleeve Classic Jersey, XL         53.99          Yellow      41.5723

So my question is why am I seeing Red color's row when I've not given this in the inner query?

b. When I am not using GROUP BY it is showing me this table....

Name                       ListPrice    Color    StandardCost
Half-Finger Gloves, S       24.49       Black      9.15932    
Half-Finger Gloves, M       24.49       Black      9.15933    
Half-Finger Gloves, L       24.49       Black      9.1593

Why is it showing me only rows for the balck color???

• Edited by Smita Sil Thursday, September 03, 2015 3:58 PM

Good day Smita Sil,

Is this homework,which you want us solve for you?

No.It is just an assignment which I downloaded from GOOGLE for practice.

This is a bad practice to ask us to do it for you :-(

This is just like homework

You should at least do as much as you can and post explain what you can do and what issue you are dealing with. The idea of homework/practice is to practice :-)

I actually hope for your sake that no one will post the final answer. It is very fast to post a solution, while going step by step and explain can take time...

Assuming you want to learn, Let's start from the beginning :-)

step one, please post queries to create the relevant tables, and to insert the sample data.

step two, Try to understand the assignment, and post the result that you expects to get.

* Next we will go to the so

In the first part you forgot about Cost > 0 condition. I would change the first statement to

SELECT Color, MIN(StandardCost) AS [MinimumStandardCost] FROM SalesLT.Product
WHERE ColorIN ('Blue','Black','Yellow') and StandardCost>0 -- find minimum among positive cost
GROUP BY Color

Now, for part 2 read the assignment one more time carefully. As you can see it's talking about using your original first query as part of the second query (as a derived table). It also tells you that you need to JOIN. So, ponder on that thought a little first and then try to write the second query.

Smita Sil, is this still an issue?

If not, please close the thread by marking one or more correct answers. If there is no answer and you found the answer elsewhere then pls post it for future users, which will have the same issue. In that case, you can mark your own response as the answer