SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Simple Example for inner and outer query needed
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

rv498
Yak Posting Veteran

USA
60 Posts

Posted - 03/04/2014 :  10:07:51  Show Profile  Reply with Quote
I never used inner query and outer query before and if you can help me I really appreciated. I don't think I should you JOIN here imo.


-- 7.b. Use a correlated subquery to find the name of customers who have placed more than 60 orders
-- The inner query is dependent on the outer query for its value.
-- 5 rows

-- 7.b. Use a correlated subquery to find the name of customers who have placed more than 60 orders
-- The inner query is dependent on the outer query for its value.
-- 5 rows

SELECT CONCAT(C.LastName,', ',C.FirstName) AS FullName
FROM dbo.DimCustomer AS C
INNER JOIN dbo.FactInternetSales AS F
ON C.CustomerKey = F.CustomerKey
WHERE F.OrderQuantity > 60


maunishq
Yak Posting Veteran

Canada
71 Posts

Posted - 03/04/2014 :  10:20:52  Show Profile  Reply with Quote
If you want to use correlated subquery then rather using JOIN, use something like this:
SELECT CONCAT(C.LastName,', ',C.FirstName) AS FullName
FROM dbo.DimCustomer AS C
WHERE C.Customerkey IN (SELECT Customerkey FROM dbo.FactInternetSales WHERE OrderQuantity > 60)

!_(M)_!

Edited by - maunishq on 03/04/2014 10:21:15
Go to Top of Page

rv498
Yak Posting Veteran

USA
60 Posts

Posted - 03/04/2014 :  10:26:17  Show Profile  Reply with Quote
Hi, maunishq, thanks for help but your code doesn't return any rows. 5 rows are need to be returned.
Go to Top of Page

maunishq
Yak Posting Veteran

Canada
71 Posts

Posted - 03/04/2014 :  10:37:32  Show Profile  Reply with Quote
First try to see how many rows this query generate:
SELECT Customerkey FROM dbo.FactInternetSales WHERE OrderQuantity > 60
THEN try to use the result into
SELECT CONCAT(C.LastName,', ',C.FirstName) AS FullName
FROM dbo.DimCustomer AS C
WHERE C.Customerkey IN (....)


See if you get 5 rows.

=======================
Not an Expert, Just a learner.
!_(M)_!
Go to Top of Page

rv498
Yak Posting Veteran

USA
60 Posts

Posted - 03/04/2014 :  10:46:42  Show Profile  Reply with Quote
SELECT Customerkey FROM dbo.FactInternetSales WHERE OrderQuantity > 60

this returns 0 rows. Hmmmm, maybe I need another table from the database.

Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 03/04/2014 :  12:02:23  Show Profile  Reply with Quote
I don't know if this is nit-picking or not, but I wouldn't consider the example by Maunishq a correlated sub-query as it isn't correlated. In my mind a query that is correlated has a reference to the dominate (outer) table, like: (SELECT Customerkey FROM dbo.FactInternetSales WHERE OrderQuantity > 60 and CustomerKey = C.CustomerKey).

The solution may be the correct one, but I think either the question is asking the wrong question or the answer is not answering the right question. Just my 2 bits.
Go to Top of Page

rv498
Yak Posting Veteran

USA
60 Posts

Posted - 03/04/2014 :  15:14:37  Show Profile  Reply with Quote
Hi, Lamprey, so where do you put your code? After the first SELECT and FROM part? Is it after JOIN (...) like this? IF it's a join statement, what do I put for 'ON' part since your code has all the necessary trappings. Anyone else can jump in if they think they know the answer. Thanks.
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 03/04/2014 :  16:10:04  Show Profile  Reply with Quote
I assume what you doing this for a class or something, so I was just pointing out that I think a Correlated Sub query needs to be correlated to something. It's possible that the teacher doesn't really know the difference or that I'm wrong; either way is probably irrelevant. But, the solution posted by Maunishq should work as a solution just fine. If you are not getting the result you want/expect then check the links below:

http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Go to Top of Page

rv498
Yak Posting Veteran

USA
60 Posts

Posted - 03/04/2014 :  17:30:03  Show Profile  Reply with Quote
Thanks Lamprey. I solved this problem but you and others helped.
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 03/05/2014 :  15:59:26  Show Profile  Reply with Quote
I just saw that SQL Server Central has an article about Correlated Queries that might be of interest:
http://www.sqlservercentral.com/articles/Stairway+Series/105972/
Go to Top of Page

rv498
Yak Posting Veteran

USA
60 Posts

Posted - 03/05/2014 :  19:52:35  Show Profile  Reply with Quote
good read, Lamprey, thanks.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000