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
 Correlated Subquery from two tables
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mallorz4
Starting Member

27 Posts

Posted - 01/19/2014 :  16:19:52  Show Profile  Reply with Quote
I've been through my textbook, online articles, youtube ... you name it! Every reference to a correlated subquery that I have found involves only one table.

Two quick questions:
1. Is the below considered a correlated subquery?
2. Can you use a JOIN in an embedded SELECT statement? I ask because I have errors near the WHERE keyword in both subqueries.

USE MyGuitarShop
SELECT EmailAddress,
	(SELECT MIN(OrderDate) FROM Orders JOIN Customers WHERE Orders.CustomerID = Customers.CustomerID) AS OldestOrder,
	(SELECT Orders.OrderID FROM Orders JOIN OrderItems WHERE Orders.OrderID = OrderItems.OrderID) AS OrderID
FROM Customers 
GROUP BY Customers.EmailAddress

mallorz4
Starting Member

27 Posts

Posted - 01/19/2014 :  16:56:46  Show Profile  Reply with Quote
Alright ... Replaced the WHERE with ON (duh).

I still get an error that states more than one value was returned so I assume the above code is not a valid correlated subquery.
Go to Top of Page

bitsmed
Constraint Violating Yak Guru

388 Posts

Posted - 01/19/2014 :  17:02:13  Show Profile  Reply with Quote
1) yes the first subquery is
2) yes. the reason you get error, is because when joining, you use "on" statement to join the fields in the tables instead of "where" statement. also the first subquery doesn't need to join the Customers table, as that table is already used in mainquery, and the second subquery you would probably only want orders for the specifik customer. second subquery also could return multiple values, which is not allowed here. you might be looking for something like:
select EmailAddress
      ,(select min(OrderDate)
          from Orders
         where Orders.CustomerID=Customers.CustomerID
       ) as OldestOrder
      ,(select min(Orders.OrderID)
          from Orders
               inner join OrderItems
                       on OrderItems.OrderID=Orders.OrderID
         where Orders.CustomerID=Customers.CustomerID
       ) as OrderID
  from Customers 
 group by Customers.EmailAddress
Go to Top of Page

mallorz4
Starting Member

27 Posts

Posted - 01/19/2014 :  20:44:19  Show Profile  Reply with Quote
Alright, so this is what I have come up with. I am pretty sure I've gotten the same results earlier with different syntax so I am going to roll with it for the time being.

One thing that isn't quite exact is that I get two entries for two particular email addresses. I thought the DISTINCT identifier would prevent that. Am I missing something there? Does that somehow relate to the fact that the two queries get ran separately?

SELECT DISTINCT EmailAddress, O.OrderID, MIN(O.OrderDate) AS OldestOrder
FROM Customers as C
	RIGHT JOIN Orders as O ON C.CustomerID = O.CustomerID
WHERE EXISTS (SELECT OrderID FROM OrderItems  as OI WHERE OI.OrderID = O.OrderID)
GROUP BY C.EmailAddress, O.OrderID
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 01/20/2014 :  06:30:23  Show Profile  Reply with Quote
quote:
Originally posted by mallorz4

Alright, so this is what I have come up with. I am pretty sure I've gotten the same results earlier with different syntax so I am going to roll with it for the time being.

One thing that isn't quite exact is that I get two entries for two particular email addresses. I thought the DISTINCT identifier would prevent that. Am I missing something there? Does that somehow relate to the fact that the two queries get ran separately?

SELECT DISTINCT EmailAddress, O.OrderID, MIN(O.OrderDate) AS OldestOrder
FROM Customers as C
	RIGHT JOIN Orders as O ON C.CustomerID = O.CustomerID
WHERE EXISTS (SELECT OrderID FROM OrderItems  as OI WHERE OI.OrderID = O.OrderID)
GROUP BY C.EmailAddress, O.OrderID



the DISTINCT is redundant here as you're already applying GROUP BY. Also it looks for disctinct combination of EmailAddress and OrderID values and not distinct EmailAddress alone. So for every customer with multiple orders you will get multiple records with same EMailAddress value


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

mallorz4
Starting Member

27 Posts

Posted - 01/20/2014 :  19:37:08  Show Profile  Reply with Quote
quote:


SELECT DISTINCT EmailAddress, O.OrderID, MIN(O.OrderDate) AS OldestOrder
FROM Customers as C
	RIGHT JOIN Orders as O ON C.CustomerID = O.CustomerID
WHERE EXISTS (SELECT OrderID FROM OrderItems  as OI WHERE OI.OrderID = O.OrderID)
GROUP BY C.EmailAddress, O.OrderID


the DISTINCT is redundant here as you're already applying GROUP BY. Also it looks for disctinct combination of EmailAddress and OrderID values and not distinct EmailAddress alone. So for every customer with multiple orders you will get multiple records with same EMailAddress value.


Visakh - Your reasoning makes sense, although I seem to get the exact same results with or without the DISTINCT classification. Can you think of another reason why I am receiving a couple sets of duplicate email addresses?


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

[/quote]
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 01/21/2014 :  05:26:04  Show Profile  Reply with Quote
quote:
Originally posted by mallorz4

quote:


SELECT DISTINCT EmailAddress, O.OrderID, MIN(O.OrderDate) AS OldestOrder
FROM Customers as C
	RIGHT JOIN Orders as O ON C.CustomerID = O.CustomerID
WHERE EXISTS (SELECT OrderID FROM OrderItems  as OI WHERE OI.OrderID = O.OrderID)
GROUP BY C.EmailAddress, O.OrderID


the DISTINCT is redundant here as you're already applying GROUP BY. Also it looks for disctinct combination of EmailAddress and OrderID values and not distinct EmailAddress alone. So for every customer with multiple orders you will get multiple records with same EMailAddress value.


Visakh - Your reasoning makes sense, although I seem to get the exact same results with or without the DISTINCT classification. Can you think of another reason why I am receiving a couple sets of duplicate email addresses?


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs



[/quote]
you missed my point
I was telling the same thing which is why I told DISTINCT is redundant
For your issue the problem is because you've mutiple orders per customer so customer details will repeat if you want to show details at ordr level.
If you want distinct per customer then you need to decide how you want to display the order details in case of multiple. You can show only one of the value by applying aggregation like first order,last order etc. Otherwise you need to show them in delimited format ie comma separated.
see this example


SELECT EmailAddress, O.OrderID, O.OrderDate AS OldestOrder
FROM Customers as C
	RIGHT JOIN (SELECT ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY OrderDate) AS Seq,* 
FROM Orders) as O ON C.CustomerID = O.CustomerID
AND O.Seq=1
WHERE EXISTS (SELECT OrderID FROM OrderItems  as OI WHERE OI.OrderID = O.OrderID)


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

mallorz4
Starting Member

27 Posts

Posted - 01/21/2014 :  06:40:21  Show Profile  Reply with Quote

[/quote]
you missed my point
I was telling the same thing which is why I told DISTINCT is redundant
For your issue the problem is because you've mutiple orders per customer so customer details will repeat if you want to show details at ordr level.
If you want distinct per customer then you need to decide how you want to display the order details in case of multiple. You can show only one of the value by applying aggregation like first order,last order etc. Otherwise you need to show them in delimited format ie comma separated.
see this example


SELECT EmailAddress, O.OrderID, O.OrderDate AS OldestOrder
FROM Customers as C
	RIGHT JOIN (SELECT ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY OrderDate) AS Seq,* 
FROM Orders) as O ON C.CustomerID = O.CustomerID
AND O.Seq=1
WHERE EXISTS (SELECT OrderID FROM OrderItems  as OI WHERE OI.OrderID = O.OrderID)


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

[/quote]

Thanks for explaining further. I will take a closer look tonight and may have to do a bit of research. I appreciate it!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 01/21/2014 :  07:24:40  Show Profile  Reply with Quote
yep..let us know if you still have any doubt.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

mallorz4
Starting Member

27 Posts

Posted - 01/21/2014 :  20:43:37  Show Profile  Reply with Quote
FYI, got it!

USE MyGuitarShop
SELECT Customers.EmailAddress, Orders.OrderID, Orders.OrderDate
FROM Customers JOIN Orders
	ON Customers.CustomerID = Orders.CustomerID
WHERE OrderDate = (SELECT MIN(OrderDate) FROM Orders 
	WHERE Orders.CustomerID = Customers.CustomerID)
ORDER BY EmailAddress


Thank you again for your help.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 01/22/2014 :  06:37:10  Show Profile  Reply with Quote
cool
Glad that you got it sorted out

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
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.08 seconds. Powered By: Snitz Forums 2000