I need to use sql code similar to Inner Join, but with the ability to use different columns in ON. Maybe there is something more pointed and specific that I can use. What do you think I would need? How can I fix?

What I want to do, in order, is:

1. Observe the next Chicago hockey matchs (Chicago-Minnesota) in the NEXT table. Chicago is featured in ClubHome

NEXT

2. In the Results table, if Chicago is in ClubHome then I extract ScoreHome for each Chicago (1, 1). If Chicago is in ClubAway then I extract ScoreAway for each Chicago (4,1). Next i want to count Chicago's points total and would like to get 1, 4, 1, 1.

3. Naturally, I'd like to get the same thing for the other teams in the ClubHome column in Next, so New York, Dallas and others that I haven't included in the example. For Chicago I would like to recover 1, 4, 1, 1, for New York I would like to recover 2, 3, 2, for Dallas I would like to recover 0, 3, 1, and other clubs in the column that I didn't enter in the example

RESULTS

4. I thought (but I could be wrong) that I need something like Inner Join, because Chicago from the ClubHome column in the Next table...must match BOTH ClubHome and ClubAway from the Results table. Maybe I'm wrong. I need the loop for x in my.fetchall() necessarily because I've already started writing the project this way using the loop

OUTPUT

I would like to get these outputs:

Chicago-Minnesota, (1, 4, 1, 1)
New York-Los Angeles,(2, 3, 2) 
Dallas-Vegas Gold, (0, 3, 1)
...and other clubs 

my = cursor.execute('''SELECT Next.ClubHhome||"-"||Next.ClubAway,
                              Results.ScoreHome, Results.ScoreAway
                       FROM Next
                       INNER JOIN Results 
                       ON
                       Next.ClubHhome = Results.ClubHhome OR Results.ClubAway
                       ''') 

for x in my.fetchall():
    print(x)

P.S: I use Sqlite

CodePudding user response：

It remains unclear exactly what you want, but I'm going to answer for when you want all the scores from each team in next.clubhome, one row per score.

Select from next, and join on results. Join on either results.clubhome OR results.clubaway. You've almost got it. Next.ClubHome = Results.ClubHome OR Results.ClubAway means (Next.ClubHome = Results.ClubHome) OR Results.ClubAway so that's going to be true anytime Results.ClubAway is true. What you want is Next.ClubHome = Results.ClubHome OR Next.ClubHome = Results.ClubAway, or Next.ClubHome in (Results.ClubHome, Results.ClubAway).

If you want to include all teams in next even if they have no rows in results, use a left join.

Then use a case statement to decide which score column to use. Check if next.clubhome matches results.clubhome or clubaway.

select
  n.clubhome,
  case
  when n.clubhome = r.clubhome then r.scorehome
  when n.clubhome = r.clubaway then r.scoreaway
  end as score
from next n
left join results r on n.clubhome in (r.clubhome, r.clubaway)

If you want one row per team, group by next.clubhome and use group_concat to concatenate all the scores into a single list per team.

select
  n.clubhome,
  group_concat(
    case
    when n.clubhome = r.clubhome then r.scorehome
    when n.clubhome = r.clubaway then r.scoreaway
    end
  ) as score
from next n
left join results r on n.clubhome in (r.clubhome, r.clubaway)
group by n.clubhome

Demonstration.