Basic SQL query question
- by user318573
Hello, I have the following query:
SELECT
Base.ReportDate,
Base.PropertyCode,
Base.FirstName,
Base.MiddleName,
Base.LastName,
Person.FirstName,
Person.MiddleName,
Person.LastName
FROM LeaseT INNER JOIN
Base ON LeaseT.LeaseID = Base.LeaseID INNER JOIN
Person ON LeaseT.TenantID = Person.ID
works fine, except there could be 0 to 'N' people in the 'Person' table for each record in the Base table, but I only want to return exactly 1 for each 'Base' record (doesn't matter which, but the one with the lowest Person.ID) would be a reasonable choice. If there is 0 rows in the person table, I still need to return the row, but with null values for the 'person' fields.
How would I structure the SQL to do that?
Thanks.
Edit: Yes, the tables are probably not structured properly, but restructuring at this time is not possible - got to work with what is there.