How can I filter a table for maximum AND minimum values - and then join that table to another one?-CodePudding

I know this is probably a very newbie question but I'm very new to SQL so please, please bare with me.

Bascially, my 'Person' table contains information like first and last name, address, date of birth, and my 'Member' table contains information specific to members (i.e. monthly renewal fee, bookings made, etc.)

MembersID is a foreign key which references Person.ID. Essentially, what I'm trying to do is filter out my Member table so that only the members who have made the least AND most bookings are displayed, then, I want to join this to the person table so that their corresponding information (i.e. their names, dates of birth) are also displayed.

I have no problem filtering out the Member table to show only the members who have the most and least bookings made - and I can also join the two tables together so that ALL members are shown.

But I do not understand how to filter the member table - and then join ONLY those filtered results to the person table... I feel like I have both pieces to the puzzle I just can't put it together.

This is what I mean:

select 
    p.FirstName, 
    p.LastName, 
    p.DateOfBirth, 
    p.[Address], 
    m.BookingsMade
from Person p
inner join Member m on p.ID = m.MemberID;

That's me joining the two tables so that all information is displayed, below is me filtering out the member table (seperately) so that only the highest and lowest bookings are displayed:

select 
    m.MemberID, 
    m.[MonthlyFee ($)], 
    m.BookingsMade
from Member m 
    where m.BookingsMade = (select min(BookingsMade) from Member)
        or m.BookingsMade = (select max(BookingsMade) from Member)
    order by BookingsMade ASC;

How can I put this together?

CodePudding user response：

You can add pre-join conditions to your ON clause, so try this:

select 
    p.FirstName, 
    p.LastName, 
    p.DateOfBirth, 
    p.[Address], 
    m.BookingsMade
from Person p
inner join Member m on p.ID = m.MemberID 
AND (m.BookingsMade = (select min(BookingsMade) from Member)
     OR or m.BookingsMade = (select max(BookingsMade) from Member))
order by BookingsMade ASC;