Составить SQL запрос (один ко многим)

Question

[Screamster]

Есть 1 таблица events спортивные события i.imgur.com/2FeuOUe.png

у нее 2 поля обвел красным

есть таблица members (хотя правильнее назвать ее будет teams - команды) i.imgur.com/7K2Y8ed.png

Как мне в запросе вместо id, что я выделил, подставить name из второй таблицы?

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

вот такой запрос возвращает 1 название i.imgur.com/9BDDo5y.png

но только я добавляю AND member_2_id = members.id и members.name as team2_name в селект

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

сразу пустой запрос получается. а мне бы рядом возле первой команды, вторую вывести.

Answer 1

[Иван]

Используйте LEFT JOIN и алиасы:

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