S
S
Screamster2014-05-07 23:44:14
SQL
Screamster, 2014-05-07 23:44:14

Compose SQL query (one to many)

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 the question

In order to leave comments, you need to log in

1 answer(s)
I
Ivan, 2014-05-07
@Screamster

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

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question