I have the following tables "PH001_Valid" this table holds static data (it never changes).
Then I have "PH001_House", this table is dynamic and new records are added all the time. In both tables there is a field named "Room" which links the tables together using an Left Outer Join. "PH001_House" also has a field which contains the date and time the record was inserted.
Because the "PH001_House" table can hold multiple records with the same room number the query returns duplicates. Is there a way I can return all the records from "PH001_Valid" and only the latest record from "PH001_House"
Left Outer Join PH001_House ON PH001_House.Room = PH001_Valid.Room
ORDER BY PH001_Valid.Room ASC
Any advice would be great.
Many thanks in advance,