World Cup Database - Build a World Cup Database

This is the step I’m having trouble with:

When you run your insert_data.sh script, it should insert a row into the games table for each line in the games.csv file (other than the top line of the file). There should be 32 rows. Each row should have every column filled in with the appropriate info. Make sure to add the correct ID’s from the teams table (you cannot hard-code the values)

Here are the columns in the games table:
game_id, year, round, winner_goals, opponent_goals, winner_id, opponent_id

Here are the different lines in the games.csv file:
year, round, winner, opponent, winner_goals, opponent_goals

Here is my code:

WINNER_ID=$($PSQL "SELECT team_id FROM teams WHERE name = '$WINNER'") 
OPPONENT_ID=$($PSQL "SELECT team_id FROM teams WHERE name = '$OPPONENT'")

INSERT_GAMES_INFO=$($PSQL "INSERT INTO games(year, round, winner_goals, opponent_goals, winner_id, opponent_id) VALUES($YEAR, '$ROUND', $WINNER_GOALS, $OPPONENT_GOALS, $WINNER_ID $OPPONENT_ID")

Here is the error I’m receiving:

ERROR: syntax error at end of input
LINE 1: …nt_id) VALUES(year, ‘round’, winner_goals, opponent_goals,

I think the problem is that I dont have WINNER_ID or OPPONENT_ID properly defined. Im not really sure how else to define them. They are both foreign keys of team_id in the teams column.

But the teams table only has 2 columns: team_id and names. This table is just a list of all the UNIQUE teams part of the tournament. This table does not know which teams are winners and losers in specific rounds. Some teams can be a winner in one round, then they can be a loser in a later round.

I dont know how to reference a particular winner or opponent listed in the games.csv file to a team_id in the teams table and then assign that winner or opponent the appropriate ID in the games table.

Heres my full code:

#! /bin/bash

if [[ $1 == "test" ]]
then
  PSQL="psql --username=postgres --dbname=worldcuptest -t --no-align -c"
else
  PSQL="psql --username=freecodecamp --dbname=worldcup -t --no-align -c"
fi

# Do not change code above this line. Use the PSQL variable above to query your database.

cat games.csv | while IFS="," read YEAR ROUND WINNER OPPONENT WINNER_GOALS OPPONENT_GOALS

do 
if [[ $WINNER != winner ]]
then
  # insert UNIQUE TEAM TO TEAMS TABLE
  INSERT_WINNER=$($PSQL "INSERT INTO teams(name) VALUES('$WINNER')")
fi

if [[ $OPPONENT != opponent ]]
then
  INSERT_OPPONENT=$($PSQL "INSERT INTO teams(name) VALUES('$OPPONENT')")
  echo $INSERT_TEAMS
 fi
  
WINNER_ID=$($PSQL "SELECT team_id FROM teams WHERE name = '$WINNER'") 
OPPONENT_ID=$($PSQL "SELECT team_id FROM teams WHERE name = '$OPPONENT'")

INSERT_GAMES_INFO=$($PSQL "INSERT INTO games(year, round, winner_goals, opponent_goals, winner_id, opponent_id) VALUES($YEAR, '$ROUND', $WINNER_GOALS, $OPPONENT_GOALS, $WINNER_ID $OPPONENT_ID")

done


Your browser information:

User Agent is: Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/105.0.0.0 Safari/537.36

Challenge: World Cup Database - Build a World Cup Database

Link to the challenge:

Maybe try to check is there already the team’s name in the teams table before insert name to it. It also helps to do “Learn SQL by Building a Student Database: Part 1” before this one