The sql below is selecting individuals from a career table. At present I get one entry for each occurrence of an individual (ie if someone has been promoted there will be two entries). The rules for the database are that if someone leaves the company or is promoted then the date _Left is set.

What I want to achieve (preferably in a single statement - ie no scripts) is only one entry for each individual, and where there are multiple entries in the underlying table all I want is the one without an _Left date, or where they all have _Left dates, the most recent.

SELECT
_ID,
_Forename +' '+_Surname AS _Name,
W._JobTitle,
W._Left,
IF(W._Left IS NULL THEN TRUE ELSE FALSE) AS _StillThere,
W._fkSites,
_Surname+', '+_Forename AS _SortName
FROM Contacts
JOIN Career W ON W._fkContacts = _ID
WHERE
W._fkCompanies = :CompanyID

Roy Lambert

Roy,

<< What I want to achieve (preferably in a single statement - ie no scripts)
is only one entry for each individual, and where there are multiple entries
in the underlying table all I want is the one without an _Left date, or
where they all have _Left dates, the most recent. >>

Try this:

SELECT
_ID,
_Forename +' '+_Surname AS _Name,
MAX(W._Left) as Left,
W._JobTitle,
IF(MAX(W._Left) IS NULL THEN TRUE ELSE FALSE) AS _StillThere,
W._fkSites,
_Surname+', '+_Forename AS _SortName
FROM Contacts
JOIN Career W ON W._fkContacts = _ID
WHERE
W._fkCompanies = :CompanyID
GROUP BY 1, 2, 4, 6, 7;

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim

Thanks but no prize, still get multiple entries for an individual

But what is

> GROUP BY 1, 2, 4, 6, 7;

The result set is used to populate a couple of grids - current employees and ex-employees so what I've done is order it by date left so the current staff are at the top and then whilst looping stuff the id into a sorted stringlist, if its already there don't put into the grid - it works and is reasonably fast.

Roy Lambert

Roy,

<< Thanks but no prize, still get multiple entries for an individual >>

Sorry, it's very hard to give an answer to these types of questions without
metadata and some understanding of what the tables look like in terms of the
rows and uniqueness of the various columns.

<< But what is

GROUP BY 1, 2, 4, 6, 7; >>

It's the way DBISAM allows you to use ordinal SELECT column positions in
ORDER BY or GROUP BY clauses.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim


>Sorry, it's very hard to give an answer to these types of questions without
>metadata and some understanding of what the tables look like in terms of the
>rows and uniqueness of the various columns.

I know and you do  very good job most of the time

Roy Lambert

Roy Lambert <roy.lambert@skynet.co.uk> wrote in
news:F457E6D7-A972-4865-8C5E-A36DBC863699@news.elevatesoft.com:

> Thanks but no prize, still get multiple entries for an individual
>
> But what is
>
>> GROUP BY 1, 2, 4, 6, 7;
>
> The result set is used to populate a couple of grids - current
> employees and ex-employees so what I've done is order it by date left
> so the current staff are at the top and then whilst looping stuff the
> id into a sorted stringlist, if its already there don't put into the
> grid - it works and is reasonably fast.
>
> Roy Lambert

Roy,

 What about Tim's SQL with

 GROUP BY 1
 ORDER BY 1, 2

IIUC this won't work in EDB but it might in DBISAM

--
Chris
(XP-Pro + Delphi 7 Architect + DBISAM 4.25 build 4 + EDB 1.04 build 3)

Roy,

<< I know and you do very good job most of the time >>

Not really, but thanks for saying so anyways.  I'm better at the internals
than I am at actually using the SQL.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim


Yeah but at least you understand the gibberish - for me its still a foreign tounge - generally spoken after to much ale

Roy Lambert

Roy,

<< Yeah but at least you understand the gibberish - for me its still a
foreign tounge - generally spoken after to much ale >>

It's actually quite nice when you start using it a bit more, and it
translates to a lot of different development environments, etc.

--
Tim Young
Elevate Software
www.elevatesoft.com

Why don't you select into a memory table, and then select distinct from the memory table?

Just a thought from a novice...

Roy Lambert <roy.lambert@skynet.co.uk> wrote:

The sql below is selecting individuals from a career table. At present I get one entry for
each occurrence of an individual (ie if someone has been promoted there will be two
entries). The rules for the database are that if someone leaves the company or is promoted
then the date _Left is set.

What I want to achieve (preferably in a single statement - ie no scripts) is only one
entry for each individual, and where there are multiple entries in the underlying table
all I want is the one without an _Left date, or where they all have _Left dates, the most
recent.

SELECT
_ID,
_Forename +' '+_Surname AS _Name,
W._JobTitle,
W._Left,
IF(W._Left IS NULL THEN TRUE ELSE FALSE) AS _StillThere,
W._fkSites,
_Surname+', '+_Forename AS _SortName
FROM Contacts
JOIN Career W ON W._fkContacts = _ID
WHERE
W._fkCompanies = :CompanyID

Roy Lambert