Using Select Distinct in Nested subquery

Question

I have a select distinct query which identifies a location ID(pk). But I want to identify the location name for the end user. I thought a subquery would return it. But it returns the value of 1. Instead of the actual location ID when i run the select distinct query separately (25). Can't seem to figure out why.

SELECT Location.LocationID, Location.Location
FROM Location
WHERE (Location.LocationID)=(
    SELECT Count(*) AS UniqueLocations
    FROM (
        SELECT DISTINCT TransactionLocation
        FROM Transactions
        WHERE (Transactions.TransactionActivity) = [Enter Activity] 
         AND (Transactions.TransactionDate) BETWEEN [Enter the start date:] AND [Enter the end date:]
    )
);

Transaction Table

TransactionID(pk)| TransactionEmployee|TransactionActivity|TransactionLocation|Date
1 | 1001 | 1 | 25 | 01/01/2014
2 | 1002 | 1 | 25 | 01/02/2014
3 | 1003 | 1 | 25 | 01/03/2014

Location Table

LocationID(pk)|Location Name
24 | Chicago
25 | Pittsburgh
26 | Boston

Thanks Mike

I'm trying to figure out what you want to achieve. You want the location where the ID equals the number of different transactionlocations where a given activity happened between two given dates ? I can't make sense out of that =/ — deroby
Yes, (SELECT Count(*) AS UniqueLocations FROM (SELECT DISTINCT TransactionLocation FROM Transactions WHERE (Transactions.TransactionActivity) = [Enter Activity] AND (Transactions.TransactionDate) Between [Enter the start date:] And [Enter the end date:])); should equal 25. — exceldude2001
Use SO's built-in markdown formatting when possible, as opposed to HTML tags (like <br> for new lines in code formatting). — Zev Spitz
Why do you assume it should equal 25 ? As far as I can tell from your example SELECT DISTINCT TransactionLocation FROM Transactions WHERE (Transactions.TransactionActivity) = [Enter Activity] AND (Transactions.TransactionDate) Between [Enter the start date:] And [Enter the end date:]) will indeed return 25, but the SELECT COUNT(*) that you put around it will return 1 because there is only 1 value, namely 25. — deroby
The reason it returns 1 and not 25 is that you use count() and there's only one item returned. — jpw

jpw jpw · Accepted Answer · 2014-03-11T21:01:24

I think maybe you might want something like this:

SELECT Location.LocationID, Location.Location
FROM Location
WHERE Location.LocationID IN 
  (SELECT DISTINCT TransactionLocation 
   FROM Transactions
   WHERE Transactions.TransactionActivity = [Enter Activity] 
   AND Transactions.TransactionDate Between [Enter the start date:] And [Enter the end date:]);

Using Select Distinct in Nested subquery

1 Answers