Basic SQL query question
Posted
by user318573
on Stack Overflow
See other posts from Stack Overflow
or by user318573
Published on 2010-05-13T18:10:18Z
Indexed on
2010/05/13
18:14 UTC
Read the original article
Hit count: 204
sql
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.
© Stack Overflow or respective owner