Jump to content

inserting using case else


shaddf

Recommended Posts

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???

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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})

Link to comment
Share on other sites

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??

 

Link to comment
Share on other sites

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 );

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.