selecting DISTINCT in a inner join statement

[Bluemonkey]

how can i select DISTINCT in side this stsament i wont DISTINCT on tblCompanyAreaType.AreaType but i cant get it i keep getting areas with teh variasions i try

"SELECT tblCompanies.PrimaryKey, tblCompanies.PlaceToEat, tblCompanyAreaType.AreaType
FROM tblCompanies INNER JOIN
tblCompanyAreaType ON tblCompanies.PrimaryKey = tblCompanyAreaType.CompanyID
WHERE (tblCompanies.PlaceToEat = '1')
ORDER BY tblCompanyAreaType.AreaType"


thanks for the help

[Bluemonkey]

after a monemt of inspration i realised i dont need the primary keyy and the placetoeat colums in the record set cos i only need the areatype so this is now my new stsaement which works fine.

sqlString = "SELECT DISTINCT tblCompanyAreaType.AreaType FROM tblCompanies INNER JOIN tblCompanyAreaType ON tblCompanies.PrimaryKey = tblCompanyAreaType.CompanyID WHERE (tblCompanies.PlaceToEat = '1') ORDER BY tblCompanyAreaType.AreaType"


thanks for looking if you did

[raf]

1. You have a fieled called PrimaryKey ? --> bad idea, no ?
2. If you include the tblCompanies.PrimaryKey and the tblCompanyAreaType.AreaType in you're recordset, you'll have wo variables with exactly the same values (since they are the joined fields), so drop one of the two.
3. You have a condition that only selects the tblCompanies.PlaceToEat = '1' --> if this is a variable with only numerical values, it should be of variabletype integer or so + if your recordset will only contain records with PlaceToEat = 1, then you can use a distinctrow or a distinct on all combinations of values of all variabels. So you'de get
SELECT DISTINCT tblCompanies.PlaceToEat, tblCompanyAreaType.AreaType
FROM tblCompanies INNER JOIN
tblCompanyAreaType ON tblCompanies.PrimaryKey = tblCompanyAreaType.CompanyID
WHERE (tblCompanies.PlaceToEat = '1') GROUP BY tblCompanies.PlaceToEat, tblCompanyAreaType.AreaType
ORDER BY tblCompanyAreaType.AreaType"

<edit> posts crossed </edit>

[Bluemonkey]

cheers raf for your answer, ve never used the group by before so its interesting to have anexaplme for it.

thanks

[raf]

Your welcome
Well. Not strictly needed here, but I use it a lott cause you can then include agregate functions like 'count', 'min', 'max', 'sum' for each record of the recordset. For instance, if you would like to sort them according to the number of times the value occured, you can include the count, order it by the count and display the count on screen. (Like in most apps where you have products categories (with the number of products behind the name) etc

[Bluemonkey]

thanks for the help