Correlated Subquery from two tables

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

Correlated Subquery from two tables

Author

Topic

mallorz4
Starting Member

27 Posts

Posted - 2014-01-19 : 16:19:52

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 - 2014-01-19 : 16:56:46

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.

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2014-01-19 : 17:02:13

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

mallorz4
Starting Member

27 Posts

Posted - 2014-01-19 : 20:44:19

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-20 : 06:30:23

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

mallorz4
Starting Member

27 Posts

Posted - 2014-01-20 : 19:37:08

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]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-21 : 05:26:04

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

mallorz4
Starting Member

27 Posts

Posted - 2014-01-21 : 06:40:21


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!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-21 : 07:24:40

yep..let us know if you still have any doubt.

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

mallorz4
Starting Member

27 Posts

Posted - 2014-01-21 : 20:43:37

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.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-22 : 06:37:10

cool
Glad that you got it sorted out

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources