Filter Query of multiple records to single records

[Ascentient]

I had originally posted a topic at the following thread for assistance however this won't work.

http://www.tek-tips.com/viewthread.cfm?qid=1202945&page=2

To me the same concept applies however, I am only working with one table now.

I have a ticket table that contains ticket information for purchases. Customers can buy at any of our locations many times. I am looking to make a query that will give me 1 record for each location the customer buys at.

Data may look like this:

CusterID Location
SMITH1 LC1
SMITH1 LC1
SMITH1 LC3
JONES LC1
JONES LC1
JONES LC1

Requested Output:
CustomerID Location
SMITH1 LC1
SMITH1 LC3
JONES LC1

I hope I have provided enough information if not please let me know.

 

[PHV]

Have a look at the DISTINCT predicate:
SELECT DISTINCT CustomerID, Location
FROM yourTable

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Ascentient]

Thanks PHV that worked.

Let me ask this. Would it be possible to only list records where the customer bought from more than one place?

CusterID Location
SMITH1 LC1
SMITH1 LC1
SMITH1 LC3
JONES LC1
MILLER LC1
MILLER LC3
MILLER LC3

Requested Output:
CustomerID Location
SMITH1 LC1
SMITH1 LC3
MILLER LC1
MILLER LC3


Since JONES only bought from one place can it be eliminated too?

Thanks for your help.

 

[lespaul]

maybe:

SELECT CustomerID, Location
FROM TableName
GROUP BY CustomerID, Location
HAVING count(*) > 1

Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com

 

[PHV]

In fact I think you want this:
SELECT CustomerID, Location
FROM (SELECT DISTINCT CustomerID, Location FROM yourTable
) AS TableName
GROUP BY CustomerID, Location
HAVING Count(*) > 1

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Ascentient]

I'll give this ago and report back.

Thanks!

 

[Ascentient]

Thanks PHV/Lespaul your suggestions appear to have worked as I have not heard back from the person using the program.

Ascent