CODEWITHSUNDEEP
sqlms-access-2007
exceldude2001
exceldude2001

Reputation: 161

Using Select Distinct in Nested subquery

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

Upvotes: 3

Views: 43835

Answers (1)

jpw
jpw

Reputation: 44881

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:]);

Upvotes: 4

Related Questions