CREATING A VIEW STATEMENT

[JEFFREY SINGLETON]

Hi,

I'm attempting to create a view statement using the following two tables: (cdinventory3 / cdtype)

mysql> SELECT * FROM cdinventory3;

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

| CDTitle | CDTypeID | CDStock |

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

| Famous Blue Raincoat | FROK | 19 |

| Blue | CPOP | 28 |

| Past Light | NEWA | 6 |

| Out of Africa | STRK | 8 |

| Fundamental | NPOP | 10 |

| Blues on the Bayou | BLUS | 11 |

| Kojiki | NEWA | 10 |

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

7 rows in set (0.04 sec)

mysql> SELECT * FROM cdtype;

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

| TypeID | TypeName |

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

| FROK | Folk Rock |

| CPOP | Classic Pop |

| NEWA | New Age |

| CTRY | Country |

| STRK | Soundtrack |

| BLUS | Blues |

| JAZZ | Jazz |

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

7 rows in set (0.00 sec)

When I initiate the following command I receive and error:

mysql> CREATE VIEW NewAge AS

-> SELECT CDTitle, CDStock FROM CDinventory3 i, cdtype t

-> WHERE i.CDTypeID = t.CDTypeID AND CDTypeName= 'New Age';

ERROR 1054 (42S22): Unknown column 't.CDTypeID' in 'where clause'



Not sure why I received this error since the CDTypeID is present in both tables.

Can someone please assist?