0

I got the following tables:

Teams

Teams

Matches

Matches

I want to get an output like:

matches.semana | teams.nom_equipo | teams.nom_equipo | Winner
       1              AMERICA         CRUZ AZUL        AMERICA
       1              SANTOS           MORELIA         MORELIA
       1               LEON            CHIVAS            LEON

The columns teams.nom_equipo reference to matches.num_eqpo_lo & to matches.num_eqpo_v and at the same time they reference to the column teams.nom_equipo to get the name of each team based on their id

Edit: I have used the following:

SELECT m.semana, t_loc.nom_equipo AS LOCAL, t_vis.nom_equipo AS VISITANTE,
       CASE WHEN m.goles_loc > m.goles_vis THEN 'home'
            WHEN m.goles_vis > m.goles_loc THEN 'visitor'
            ELSE 'tie'
       END AS Vencedor 
FROM matches AS m
JOIN teams AS t_loc ON (m.num_eqpo_loc = t_loc.num_eqpo)
JOIN teams AS t_vis ON (m.num_eqpo_vis = t_vis.num_eqpo)
ORDER BY m.semana;

But as you can see from the table Matches in row #5 from the goles_loc column (home team) & goles_vis (visitor) column, they have 2 vs 2 (number of goals - home vs visitor) being a tie but and when I run the code I get something that is not a tie:

Matches' score

Matches

Resultset from Select:

Result

I also noticed that since the row #5 the names of both teams in the matches are not correct (both visitor & home team). So, the Select brings correct data but in other order different than the original order (referring to the order from the table matches)

The order from the second week must be:

        matches.semana | teams.nom_equipo | teams.nom_equipo |   Winner
  5            2              CRUZ AZUL         TOLUCA            TIE
  6            2              MORELIA           LEON              LEON
  7            2               CHIVAS           SANTOS            TIE

Row 8 from the Resultset must be Row # 5 and so on.

Any help would be really thanked!

Improve this question

1 Answer 1

Reset to default
2

When doing a SELECT which includes null for a column, that's the value it will always be, so winner in your case will never be populated.

Something like this is probably more along the lines of what you want:

SELECT m.semana, t_loc.nom_equipo AS loc_equipo, t_vis.nom_equipo AS vis_equipo,
       CASE WHEN m.goles_loc - m.goles_vis > 0 THEN t_loc.nom_equipo
            WHEN m.goles_vis - m.goles_loc > 0 THEN t_vis.nom_equipo
            ELSE NULL
       END AS winner 
FROM matches AS m
JOIN teams AS t_loc ON (m.nom_eqpo_loc = t.num_eqpo)
JOIN teams AS t_vis ON (m.nom_eqpo_vis = t.num_eqpo)
ORDER BY m.semana;

Untested, but this should provide the general approach. Basically, you JOIN to the teams table twice, but using different conditions, and then you need to calculate the scores. I'm using NULL to indicate a tie, here.

Edit in response to comment from OP:

It's the same table -- teams -- but the JOINs produce different results, because the query uses different JOIN conditions in each JOIN.

The first JOIN, for t_loc, compares m.nom_eqpo_loc to t.num_eqpo. This means it gets the teams rows for the home team.

The second JOIN, for t_vis, compares m.nom_eqpo_vis to t.num_eqpo. This means it gets the teams rows for the visting team.

Therefore, in the CASE statement, t_loc refers to the home team, while t_vis refers to the visting one, enabling both to be used in the CASE statement, enabling the correct name to be found for winning.

Edit in response to follow-up comment from OP:

My original query was sorting by m.semana, which means other columns can appear in any order (essentially whichever Postgres feels is most efficient).

If you want the resulting table to be sorted exactly the same way as the matches table, then use the same ORDER BY tuple in its ORDER BY.

So, the ORDER BY clause would then become:

ORDER BY m.semana, m.nom_eqpo_loc, m.nom_eqpo_vis

Basically, the matches table PRIMARY KEY tuple.

Improve this answer
Sign up to request clarification or add additional context in comments.

8 Comments

Sure, no problem. Added to answer. Also, you'll want to switch matches.semana to m.semana -- that's what's leading to your new error.
I wonder why some of the names of the teams do not match and because of that none of the subsequent scores do :/
Did the ORDER BY changes I added to the answer show the results you were expecting?
Yeah. The Resultset from Select screenshot is the one being executed with the ORDER BY instruction and the order is the opposite. The first record is the last shown and so on :/
Not sure I quite understand... they still aren't in the order you are expecting? What order are they in with my changes above vs. what you are expecting?
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.