How can I change value of 1 table from another table depending on values that represent each other in different data types

Question

0.00/5 (No votes)

See more:

I have 2 tables

Table 1
Teams : List all teams

TeamsId  |  TeamName  |  TeamAbv  | TeamLogo   |
--------------------------------------------------
     1     |   Eagles   |   PHI     | eagles.png |
-------------------------------------------------
     2     |   Ravens   |   BAL     | ravens.png |

etc.. for all 32 teams

Table 2
Schedule : I input the team Id that is represented in the Teams Table
My table has 15 weeks

ScheduleId  |  Week  | GameHome_1  |  GameAway_1  |GameHome_2  |  GameAway_2  | 
--------------------------------------------------------------------------------
      1       |  Week1 |     3       |      7       |     5      |      1       |
---------------------------------------------------------------------------------
      2       |  Week2 |     11      |      6       |     8      |      4       |

I'm trying to write a query that would replace the TeamId in the Schedule Table with the Team Name from the Teams Table then display it on my site. But I need the same format. For example I need the column names to stay the same 'GameHome_1' 'GameAway_1' etc so that I can have the data display in the correct spots on my site.

I know I'm supposed to show what I tried but so far I haven't come close to what I need.
The code below only separates the Home & Away teams but only displays the values in 2 columns. I need my columns to stay as they are.

Any help would be appreciated. Thanks

What I have tried:

SELECT Home.TeamName AS HomeTeam, Away.TeamName AS AwayTeam
FROM Schedule Schd
INNER JOIN Teams Home
ON Schd.HomeTeam = Home.TeamsId
INNER JOIN Teams Away
ON Schd.AwayTeam = Away.TeamsId

Generates this table

     | HomeTeam  |  AwayTeam  |
------------------------------
  1  |   Eagles  |   Ravens   |
------------------------------
  2  |    Browns |   Colts    |
------------------------------

etc

Posted 20-May-18 15:02pm

Commish13

Updated 21-May-18 8:17am

Add a Solution

Comments

Santosh kumar Pithani 21-May-18 2:15am

Your question is not clear please show expected output

2 solutions

Solution 1

SELECT Home.TeamName AS HomeTeam, Away.TeamName AS AwayTeam

only lists two columns as the output - so that's what you get.

It sounds to me like you should create a VIEW and use that [SQL CREATE VIEW[^]. The view will always reflect the state of the combined tables as they change. You can query the view just as you'd query a table.

So, user your JOINS to create a view and specify all of the columns you wish to see.

Posted 21-May-18 3:25am

W∴ Balboos, GHB

Comments

Maciej Los 22-May-18 2:46am

5ed!

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard Deeming · Accepted Answer · 2018-05-21T08:18:00

Something like this:

SQL

SELECT 
    Schd.ScheduleId,
    Schd.Week,
    Home1.TeamName As GameHome_1,
    Away1.TeamName As GameAway_1,
    Home2.TeamName As GameHome_2,
    Away2.TeamName As GameAway_2
FROM 
    Schedule Schd
    LEFT JOIN Teams Home1
    ON Home1.TeamsId = Schd.GameHome_1
    LEFT JOIN Teams Away1
    ON Away1.TeamsId = Schd.GameAway_1
    LEFT JOIN Teams Home2
    ON Home2.TeamsId = Schd.GameHome_2
    LEFT JOIN Teams Away2
    ON Away2.TeamsId = Schd.GameAway_2
ORDER BY
    Schd.Week
;

NB: If you're guaranteed to always have all four team IDs set on every schedule row, you can use INNER JOIN. If there's any possibility that one or more of the team IDs will not be set, then you need to use LEFT JOIN.