Tim,

I have 2 tables below.

ContactRoles (ID, ContactID, RoleTypeID)
RoleTypes (ID, Name)


I wish to list the roles for a contact, say Fred is an Accountant,Conveyancer

Query 1 & Query 2 below give the same results when Fred has some roles assigned.  If Fred
does not have any roles then Query 1 and Query 2 give different results.


SELECT *
  FROM ContactRoles
     INNER JOIN RoleTypes ON ContactRoles.RoleTypeID = RoleTypes.ID
  WHERE ContactID = 10968

Gives an empty result set.

Query 1
---------

SELECT List(Name) AS ContactRoleList
  FROM ContactRoles
     INNER JOIN RoleTypes ON ContactRoles.RoleTypeID = RoleTypes.ID
  WHERE ContactID = 10968
  GROUP BY ContactID

Gives a NULL ContactRoleList which is correct.

Query 2
---------

Take the GROUP BY clause out, then obtain different results.

SELECT List(Name) AS ContactRoleList
  FROM ContactRoles
     INNER JOIN RoleTypes ON ContactRoles.RoleTypeID = RoleTypes.ID
  WHERE ContactID = 10968

Gives a ContactRoleList of Vendor Solicitor - Vendor Solictor is the first item in
ContactRoles table.

Richard Harding

Richard,

<< Take the GROUP BY clause out, then obtain different results. >>

Got it.  A fix will be in the next build.

Thanks,

--
Tim Young
Elevate Software
www.elevatesoft.com

>> Got it.  A fix will be in the next build.

Thanks Tim . . . .


Richard Harding