Basic SQL query question

Question

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.

What database? This is the 'max from group' again, except instead of max you want min. — Mark Byers
– Mark Byers, Commented May 13, 2010 at 18:12
What exactly are you trying to do, just return one random person from each base? The way you have it now, I think it is going to return every single person no matter what you do since the person fields are in the select. — ryanulit
– ryanulit, Commented May 13, 2010 at 18:17

John Allers · Accepted Answer · 2010-05-13 18:56:03Z

3

Does this work?

SELECT     
  Base.ReportDate, 
  Base.PropertyCode, 
  Base.FirstName, 
  Base.MiddleName, 
  Base.LastName, 
  Person.FirstName, 
  Person.MiddleName, 
  Person.LastName
FROM  Base
LEFT JOIN (
    SELECT LeaseID, MIN(TenantID) AS [TenantID]
    FROM LeaseT
    GROUP BY LeaseID) AS [LeaseT_SinglePerson] ON Base.LeaseID = [LeaseT_SinglePerson].LeaseID
LEFT JOIN Person ON [LeaseT_SinglePerson].TenantID = Person.ID

edited May 13, 2010 at 18:56

answered May 13, 2010 at 18:48

John Allers

3,1322 gold badges31 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Pranay Rana · Accepted Answer · 2010-05-13 18:34:37Z

3

Following may be helpful to you

SELECT     
  Base.ReportDate, 
  Base.PropertyCode, 
  Base.FirstName, 
  Base.MiddleName, 
  Base.LastName, 
  d.FirstName, ou

  d.MiddleName, 
  d.LastName
FROM  LeaseT INNER JOIN
   Base ON LeaseT.LeaseID = Base.LeaseID INNER JOIN
left outer join   
 (select min(personid) as ID from person group by personid) as d 
on 
 LeaseT.TenantID = d.ID 
left outer join 
 (select FirstName, 
  MiddleName, 
  LastName from person) d1
on
  LeaseT.TenantID = d1.ID

edited May 13, 2010 at 18:34

answered May 13, 2010 at 18:21

Pranay Rana

177k37 gold badges244 silver badges267 bronze badges

2 Comments

devio Over a year ago

I guess LeaseT should be left-joined since it is the connection between Base and Person

Pranay Rana Over a year ago

sorry to say but i am not getting you

HLGEM · Accepted Answer · 2010-05-13 18:20:37Z

1

Use a left join

answered May 13, 2010 at 18:20

HLGEM

97k15 gold badges120 silver badges191 bronze badges

Collectives™ on Stack Overflow

Basic SQL query question

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related