Sort SQL query with a field I can't make visible

[Max Moor]

Hi All,
Here's the story...

I have a table of items on which I run an SQL query. This table is
linked to another table that gives me names of people associated with each
item. Some items have more than one person associated with them, so the
table returns more than one record for some items.

This is a problem. I want to be able to filter and sort the query
based on this linked "fldPerson" field, though.

I found that I can leave fldPerson out of the SELECT clause, and
still use it in the WHERE clause for filtering. The double records are
gone, and I can still filter on it. Very Cool!

It doesn't appear that I'm as lucky with using fldPerson in the ORDER
BY clause though. I suppose it makes sense that you can't sort on a field
that you don't select. That understanding doesn't help me get everything I
want though.

Can anyone give me any advice on how to write the SQL so that:

1. I don't get the double records returned by the query because of multiple
people associated with a single item.

2. I can still filter on a person's name (returning only records associated
with that name)

3. I can sort on the person's name.

Any thoughts? Please?

- Max

 

[gandalf]

perhaps

select distinct

might work