Mysql – How do i specify a type for a new column through a select statement

MySQL

I'm trying to create a new view from an existing table which as 2 columns:

CREATE TABLE writers (
  movie_id integer NOT NULL REFERENCES movies,
  person_id integer NOT NULL REFERENCES people,
  PRIMARY KEY (movie_id,person_id)
) ;

I'm trying to make a view which adds a new column and a default character value so I want a the new to have the columns movie_id, person_id, position_name and I need position_name to be of type character so that I can union this view with another view.

Is this possible to do?

Best Answer

Yes it is possible:

create view ... as (
    select movie_id, person_id, cast(null as char(1)) as position_name
    from writers
);

I assumed that null (which is part of any type) is what is causing you the problem. Replace null with whatever if that is not the case

Related Solutions

Mysql – How to automate insertion of new column in Mysql table

primary key...will be the concatenation of two field values within the table.

no NO NO!!!!!

This breaks all the rules of database normalization (well, rule 1 and rule 2 actually) and it's totally unnecessary! It uses more storage, makes your queries less efficient, and may cause bugs in your processing.

Use the existing columns as the primary key.

Presumably it doesn't have a primary key declared already.

Check that you've got no duplicates in the table:

SELECT ContractNum, PlanId, COUNT(*) 
FROM ContractPlans
GROUP BY ContractNum, PlanId
HAVING COUNT(*)>1;

Fix the data if you need to, then

ALTER TABLE ContractPlans
ADD PRIMARY KEY (ContractNum, PlanId);

Mysql – Finding rows for a specified date range

Another way to get the result is this. It finds first all groups that the teacher has surely taught (or is going to) by checking that she has started within the month and then in another subquery it finds - for every group - the last teacher that started at the first day of the month or earlier.

With the unique index you have on the table, the second subquery should be quite efficient. The first subquery would benefit from an index on (teacherid, startdate, groupid):

SELECT groupid
FROM pupilgroupteacher
WHERE teacherid = @teacher 
  AND startdate >= @month + INTERVAL 1 DAY
  AND startdate < @month + INTERVAL 1 MONTH

UNION DISTINCT

SELECT gg.groupid
FROM 
    ( SELECT DISTINCT groupid
      FROM pupilgroupteacher
    ) AS gd
  JOIN pupilgroupteacher AS gg
    ON  gg.groupid = gd.groupid
    AND gg.startdate =
        ( SELECT MAX(gi.startdate)
          FROM pupilgroupteacher AS gi 
          WHERE gi.groupid = gd.groupid
            AND gi.startdate < @month + INTERVAL 1 DAY
        )
WHERE gg.teacherid = @teacher ;

Best Answer

Related Solutions

Mysql – How to automate insertion of new column in Mysql table

Mysql – Finding rows for a specified date range

Related Question