inserting using case else

[shaddf]

iam trying to insert a summary into a table.Is it possible to write a case condition inside an insert statement like so:

insert into match_summ(MatchID ,match_venueID,stadium_LocationID,SeasonID,CompetitionID,Team_catId)values('5','6','7','8','9',
          CASE WHEN homeid = l_team_id THEN teamcat_id=1 END ,
        CASE WHEN l_team_id = awayid THEN teamcat_id=2 
            ELSE teamcat_id= 0 END );

Is what iam trying to do possible??What is the best way to do it???

[Barand]

Regarding CASE statements, yes you can use them in an insert.

 

BUT whether the one you propose is feasible is impossible to say without knowing what you are trying to accomplish. Where do "home_id", "away_id" and "l_team_id" come from in that statement?

[shaddf]

Regarding CASE statements, yes you can use them in an insert.

 

BUT whether the one you propose is feasible is impossible to say without knowing what you are trying to accomplish. Where do "home_id", "away_id" and "l_team_id" come from in that statement?

home_id is the home team team in the matches table and away_id is the away team in the matches table(the new generated matchID record).l_team_id is the team_id from the team-categorysummary(teamId int,categoryid int) table

[Barand]

But the only table reference by that query is "match_summ" (the one you are inserting into).

 

As mentioned, what are you trying to do?

Edited February 17, 2017 by Barand

[shaddf]

But the only table reference by that query is "match_summ" (the one you are inserting into).

 

As mentioned, what are you trying to do?

how can I insert into a table given  three field values are known but the last is dependent on the team category to which the home team or away team belong to. in match_summ table;

MatchID ,match_venueID,stadium_LocationID,SeasonID,CompetitionID, are known and straight forward,but the teamcatid is based on if(hometeamid{is in table a,then set teamcatid=the catid in table a})

[Barand]

A prerequisite to writing a working query is knowledge of the table structures and relationships.

 

So how about providing that information?

[shaddf]

A prerequisite to writing a working query is knowledge of the table structures and relationships.

 

So how about providing that information?

teamcat table

+-----------+-----------+

| TeamID    | Team_catId |

+-----------+-----------+

|         1 |         23 |

|         2 |         34 |

|         3 |         45 |

+-----------+-----------+

 

matches table

+-----------+-----------+------+------+--------------+------+--------------+

| HomeScore | AwayScore | g_id | a_id | h_name       | h_id | a_name       |matchID

+-----------+-----------+------+------+--------------+------+--------------+

|         0 |         0 |   11 |    6 | Seowa fc    |    7 | Seowa U-14  |    12

|         0 |         0 |   12 |    7 | Seowa U-14  |    6 | Seowa fc    |    13

|         0 |         0 |   13 |    8 | Teowa fc    |    6 | Seowa fc    |    14

+-----------+-----------+------+------+--------------+------+-------------

 

insert into match_summ(MatchID ,match_venueID,stadium_LocationID,SeasonID,CompetitionID,Team_catId)values('14','6','7','8','9',

          CASE WHEN a_id = teamcat.TeamID THEN Teamcat_id END ,

        CASE WHEN teamcat.TeamID = h_id THEN Teamcat_id=23

            ELSE teamcat_id= 0 END );

iam trying to do this:

if the awayteam id  a_id or hometeam id h_id exists in teamcat table then set Teamcat_id to the value of Teamcat_id for this id into the summary table else if empty enter 0.

 

Also  how can i account for a situation where a game both the away team id and home team id  exists in teamcat table i.e 1 vs 2. Will two recods be recorded in the team summary table to account for the two different categories??

 

[Barand]

According to your data, none of the teams in the matches table have a corresponding team_catid in the catID table. Is this a possible situation?

[Barand]

 

if the awayteam id  a_id or hometeam id h_id exists in teamcat table then set Teamcat_id to the value of Teamcat_id

 

 

CASE WHEN teamcat.TeamID = h_id THEN Teamcat_id=23

 

Which is it?

[shaddf]

Which is it?

it is the one teamid that is for the  home team(h_id) Vs away team(a_id) in a game(matches table)

Edited February 19, 2017 by shaddf

[shaddf]

Which is it?

as the table grows of course it will have them,and with that in mind ,ijust need how to write the query the correct wayusing insert

[shaddf]

According to your data, none of the teams in the matches table have a corresponding team_catid in the catID table. Is this a possible situation?

is this syntax correct

insert into match_summ(MatchID ,match_venueID,stadium_LocationID,SeasonID,CompetitionID,Team_catId)values('14','6','7','8','9',

        select  CASE WHEN  teamcat.TeamID=a_id  THEN Teamcat_id=teamcat.Teamcat_id END ,

        CASE WHEN teamcat.TeamID = h_id THEN Teamcat_id=teamcat.Teamcat_id

            ELSE teamcat_id= 0 END FROM teamcat );

[Barand]

No, you are still pulling the a_id and h_id magically from nowhere.

 

Try

INSERT INTO match_summ(MatchID ,match_venueID,stadium_LocationID,SeasonID,CompetitionID,Team_catId)
SELECT 14,6,7,8,9, IFNULL(team_catid, 0)	
FROM
    (
    SELECT a_id as teamid
    FROM matches
    UNION
    SELECT h_id as teamid
    FROM matches
    ) teams
    LEFT JOIN teamcat tc USING (teamid);

I'd be interested in seeing the entire data model for this application. It has a very weird feel to it.