Return rows without duplicates

[jakeyg]

select Users.* from Users
inner join Property on Users.UserID = Property.JAID
where Groupid = 24
order by Property.lot

This query brings back all the records I want but with some duplicate rows which I want to exclude.

I can get SELECT DISTINCT to work for 1 column queries, but not multiple ones

help please

 

[micha123]

the SELECT DISTINCT works on all columns, for example:

SELECT DISITNCT FLD1, FLD2, FLD3 FROM ...

Selects each combination of the 3 fields FLD1,FLD2,FLD3 only once.

The same thing goes for SELECT DISTINCT * FROM ...

"A long life is when each day is full and every hour wide!"
(A Jewish Mentor)
M.

 

[jakeyg]

that's what I thought

must be something else wrong somewhere

thanks

 

[jsteph]

If you join on Property table, which appears to be in a one-to-many relationship, you'll get multile user records for each time that user is in Property table. You might try using a subquery, ie,

select * from users where userid in (select jaid from property where groupid = 24)

To get the ORDER BY to work here will take some extra work...if it's that important to have that sort order let me know,
--Jim

 

[jakeyg]

It's for an email list to clients, the sort order is just to make my bugfixing easier ;-)

They've decided to do it manually now, so that's 2 days work wasted but I'll try it for myself later to see what comes out (so I don't have to ask the same stoopid question again)

thanks