distinct not working for me..

[john6767]

ok i have some sql and i am trying to show all 'people' in my database, the problem arises because i am joing an 'areas' table which has a one to many relationship with my 'people'(in this case tradesmen and the areas is the areas that they service..)

so i am getting as a result a list of all tradesmen and they are repeated for evey area that they service, how do i fix my joins so that it will show one tradesmenID even if there are multiple areas..

heres my sql:

SELECT distinct t.tradesmenID, t.businessName, tr.tradeName,t.tradeID, a.areaID
FROM tradesmen t INNER JOIN tradesmenarea a on t.tradesmenID = a.tradesmenID INNER JOIN areas ar on a.areaID = ar.areaID INNER JOIN trades tr ON t.tradeID = tr.tradeID
ORDER BY t.businessName ASC

 

[feherke]

Hi

how do i fix my joins so that it will show one tradesmenID even if there are multiple areas..

Do not display the field from the area table ?

Why did you included the a.areaID field ? And even if you get distinct peoples, what you expect to find in that areaID column ?

I would try to use [tt]group by[/tt] instead of [tt]distinct[/tt] and use an aggregate function on that a.areaID field, for example [tt]min()[/tt].

[b]select[/b]
t.tradesmenID, t.businessName, tr.tradeName,t.tradeID, min(a.areaID)

[b]from[/b] tradesmen t
[b]inner join[/b] tradesmenarea a [b]using[/b] (tradesmenID)
[b]inner join[/b] areas ar [b]using[/b] (areaID)
[b]inner join[/b] trades tr [b]using[/b] (tradeID)

[b]group by[/b] t.tradesmenID, t.businessName, tr.tradeName,t.tradeID

[b]order by[/b] t.businessName [b]asc[/b]

( I changed the [tt]join[/tt] conditions from [tt]on[/tt] to [tt]using[/tt] just because usually is faster. )

Feherke.

http://rootshell.be/~feherke/

 

[john6767]

thanks, i didn't know about using or group by, great help! yes it doesn't matter what is in the areaID but i need it for a search i'll include in the 'where' clause later..