Compose SQL query (one to many)

Question

SQL

There is 1 events table sports events i.imgur.com/2FeuOUe.png
it has 2 fields outlined in red
there is a members table (although it would be more correct to call it teams - teams) i.imgur.com/7K2Y8ed.png
Like me in the query instead of id, what I highlighted, substitute name from the second table?

SELECT title, events.url, events.date, types.name as type_name, tournaments.name as tournaments_name, members.name as team1_name
    FROM events, types, tournaments, members
    WHERE events.type_id = types.id
    AND events.tournament_id = tournaments.id
    AND member_1_id = members.id
    ORDER BY events.date

here is a query that returns 1 name i.imgur.com/9BDDo5y.png
but only I add AND member_2_id = members.id and members.name as team2_name to the select

SELECT title, events.url, events.date, types.name as type_name, tournaments.name as tournaments_name, members.name as team1_name, members.name as team2_name
    FROM events, types, tournaments, members
    WHERE events.type_id = types.id
    AND events.tournament_id = tournaments.id
    AND member_1_id = members.id
    AND member_2_id = members.id
    ORDER BY events.date

immediately an empty request is obtained. and I would be next to the first team, bring out the second.

Answer 1

Use LEFT JOIN and aliases:

SELECT event.title, event.url, event.date, type.name as type_name, tournament.name as tournament_name, team1.name AS team1_name, team2.name AS team2_name
FROM events event
LEFT JOIN types type ON event.type_id = type.id
LEFT JOIN tournaments tournament ON event.tournament_id = tournament.id
LEFT JOIN members team1 ON event.member_1_id = team1.id
LEFT JOIN members team2 ON event.member_2_id = team2.id
ORDER BY event.date