Information générale
Catégorie:
Base de données, Tables, Vues, Index et syntaxe SQL
One thing you can do is to create a view of the position table
CREATE VIEW vPosition AS
SELECT position.possid, MAX(position.posdate) as posdate;
FROM position;
GROUP BY position.posid
Then your select will be:
select a.Actsid, a.Actssn, a.Actfname, a.Actmname, a.Actlname, p.posname, p.posdate;
from actives a left outer join vPosition p ON a.ACTSID = p.POSSID;
where possid = a.actsid ;
order by actlname
Your original query would work with SQL Server and Oracle, but not VFP. It doesn't allow sub-queries in a query.
>Im not the best sql person, probably because I dont have must experiance with it yet but, anyway i need to get this query...
>
>I have a table of employees and a seprate table of positions.
>The positions table has the id, name of the position, and date started. The most current date is the current position
>
>I need to select the employee along with their current position (just their current position) this is what I came up with.
>
>
>select a.Actsid, a.Actssn, a.Actfname, a.Actmname, a.Actlname, p.posname, p.posdate;
> from actives a left outer join position p ON a.ACTSID = p.POSSID;
> where p.posdate = (select top 1 po.posdate from position po where possid = a.actsid order by posdate desc);
> order by actlname
>
>This subquery selects the most current date in the position table.
>select top 1 po.posdate from position po where possid = a.actsid order by posdate desc
>
>Am I doing this totally wrong. I keep getting an error and it wount run...
>
>let me know if i need to explain more
>
>Thanks in advance...
>-Eric
Précédent
Suivant
Répondre
Voir le fil de ce thread
Voir le fil de ce thread à partir de ce message seulement
Voir tous les messages de ce thread
Voir tous les messages de ce thread à partir de ce message seulement