How to select one record from many

[skipScout]

I thought this would be very simple, but I can't seem to figure it out ...

Here's the scenario:
I have a users table and a phones table. The phones table can contain several phone records for a unique user, eg:
phID usID phone type
1 1 555-1212 h
2 1 555-2121 w
3 2 555-9955 h

I would like to return only one phone record per user (phone type doesn't matter). My current query ...

SELECT a.firstName, a.lastName, b.phone
FROM users a LEFT OUTER JOIN
phones b ON a.usID=b.usID

... returns 3 records. I would like it to return 2 records.

What can I do?

Thanks in advance.

 

[LKBrwnDBA]

Use the MAX or MIN function with GROUP BY usID.


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[skipScout]

LKBrwnDBA, you make it look so easy ;-)

Thanks for the solution.

 

[r937]

actully, LKBrwnDBA made it look a lot simpler than it actually is

if the GROUP BY is just on the usID column, then you can select MAX(phone) but you can not also select the other columns, i.e. a.firstName, a.lastName





rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[LKBrwnDBA]

You could always use an inline view:

SELECT ... FROM users
  LEFT OUTER JOIN (
  SELECT usID, MAX(phone) phone FROM phones 
   GROUP BY usID)...

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[ckabi]

I would like to do something similar...

from this table:
phID usID phone
1 1 555-1212
2 1 555-2121
3 2 555-9955

I want this view:
usID calcphones
1 555-1212 555-2121
2 555-9955

How can I do this?