CODEWITHSUNDEEP
sqlsql-serverjoinself-join
LaurinSt
LaurinSt

Reputation: 982

Self join on joined table

My query looks like

Select m.cw_sport_match_id as MatchId,
       m.season_id as SeasonId,
       s.title as SeasonName,
       c.title as ContestName
from dbo.cw_sport_match m
inner join dbo.cw_sport_season s
  ON m.season_id = s.cw_sport_season_id
inner join dbo.cw_sport_contest c
  ON m.contest_id = c.cw_sport_contest_id
Where s.date_start <= GETDATE() AND s.date_end >= GETDATE()
order by s.date_start

No i need the name parent of the sport_contest (if there is one, it can be null). So basically a self join but no on the same table as the query is for. All the examples that i find do the self join are not done on another table. can any sql pro help me? So how can i join the cw_sport_season itself with the season_parent_id and get the title of it?

Upvotes: 1

Views: 206

Answers (1)

sgeddes
sgeddes

Reputation: 62831

If I'm understanding your question correctly, you want to outer join the cw_sport_season table to itself using the season_parent_id field. Maybe something on these lines:

Select m.cw_sport_match_id as MatchId,
       m.season_id as SeasonId,
       s.title as SeasonName,
       parent.title as ParentSeasonName,
       c.title as ContestName
from dbo.cw_sport_match m
inner join dbo.cw_sport_season s
  ON m.season_id = s.cw_sport_season_id
inner join dbo.cw_sport_contest c
  ON m.contest_id = c.cw_sport_contest_id
left join dbo.cw_sport_season parent
  ON s.season_parent_id = parent.cw_sport_season_id
Where s.date_start <= GETDATE() AND s.date_end >= GETDATE()
order by s.date_start

Upvotes: 2

Related Questions