Flat query

[CroCrew]

Hello Everyone,

First I would like to thank anyone that helps me with this question.

I am looking for a query that will return one row of data for each person based on the “IssuedTo” column. Let me explain:

I have two tables and the first is the “Tickets” table and the second is the “People” table. Below are the two tables.

Tickets:

KeyID IssuedTo Sold  
1     1        False        
2     3        False        
3     1        False        
4     2        False        
5     2        False        
6     3        False        
7     3        False        
8     2        False        
9     1        False        
10    3        False

People:

KeyID Name
1     Sam
2     Kim
3     Dave

This is the query that I have so far:

SELECT b.Name as 'Ticket Owner', a.KeyID as 'Lowest Ticket#', a.KeyID as 'Highest Ticket#' FROM Tickets a JOIN People b ON (a.IssuedTo = b.KeyID)

Is it possible to get this results from a single query?:

Ticket Owner Lowest Ticket# Highest Ticket#
Sam          1              9
Dave         2              10
Kim          4              8

oh, the ticket number is the KeyId field in the Tickets table.


Again; thanks for the help,
CroCrew~

[ck9663]

Could you explain how you got those numbers? Are those counts? min? max?

~~ CK

[CroCrew]

I think your asking about the "Lowest Ticket#" and "Highest Ticket#" right?

The numbers come from the Tickets table's KeyID field. The KeyID fields are unique auto incrementing fields starting with the number 1.

So for the person Sam he has three records in the tickets table. row 1, row 3, and row 9. 1 would be his "Lowest Ticket#" and 9 would be his"Highest Ticket#".

understand?

Thanks for looking at my question.

CroCrew~

[ck9663]

Got it...The KeyID got me confused. The KeyId on your table is the same name as your KeyId on your persons table. Which, on first glance, tells me it's the link on those tables.

Try something like

SELECT IssuedTo, min(KeyId) as LowestTicket, max(KeyId) as HighestTicket
from Tickets t
inner join Person p on t.IssuedTo = p.KeyId
group by IssuedTo

This is a INNER JOIN. Which means only tickets with assigned person ID that exists on your persons table will be returned. Make it a LEFT JOIN as necessary.

Happy Coding!!!

~~ CK

[Jerry Winston]

Just GROUP BY Person.Name and use MIN and MAX functions. Am I missing something in your requirement?