Simple Example for inner and outer query needed

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Simple Example for inner and outer query needed

Author

Topic

rv498
Yak Posting Veteran

60 Posts

Posted - 2014-03-04 : 10:07:51

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

71 Posts

Posted - 2014-03-04 : 10:20:52

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)_!

rv498
Yak Posting Veteran

60 Posts

Posted - 2014-03-04 : 10:26:17

Hi, maunishq, thanks for help but your code doesn't return any rows. 5 rows are need to be returned.

maunishq
Yak Posting Veteran

71 Posts

Posted - 2014-03-04 : 10:37:32

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)_!

rv498
Yak Posting Veteran

60 Posts

Posted - 2014-03-04 : 10:46:42

SELECT Customerkey FROM dbo.FactInternetSales WHERE OrderQuantity > 60

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

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-03-04 : 12:02:23

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.

rv498
Yak Posting Veteran

60 Posts

Posted - 2014-03-04 : 15:14:37

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.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-03-04 : 16:10:04

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

rv498
Yak Posting Veteran

60 Posts

Posted - 2014-03-04 : 17:30:03

Thanks Lamprey. I solved this problem but you and others helped.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-03-05 : 15:59:26

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/

rv498
Yak Posting Veteran

60 Posts

Posted - 2014-03-05 : 19:52:35

good read, Lamprey, thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources