General information
Category:
Databases,Tables, Views, Indexing and SQL syntax
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
Previous
Next
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only