Management Studio will not allow me to create a view

Hi there,
When I run the following query I get the correct result.
select * from Inventory As I Full Outer Join Publisher As P on
I.ID=P.InventoryID
However, when I try to create a view with the same select statement I get
the following error:
Msg 4506, Level 16, State 1, Procedure InventoyPublisherView, Line 2
Column names in each view or function must be unique. Column name 'ID' in
view or function 'InventoyPublisherView' is specified more than once.
The CREATE VIEW statement I'm using is:
CREATE VIEW InventoyPublisherView AS
(SELECT * FROM Inventory AS I FULL OUTER JOIN Publisher AS P ON
I.ID=P.InventoryID)
Many thanks in advance for the help. Very much appreciatedName the columns in the SELECT statement. Both tables has a column named ID,
and the view cannot
have two columns with the same name.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Chris L" <ChrisL@.discussions.microsoft.com> wrote in message
news:0C10F52D-2845-4F6B-92B7-648A5D1FBC50@.microsoft.com...
> Hi there,
> When I run the following query I get the correct result.
> select * from Inventory As I Full Outer Join Publisher As P on
> I.ID=P.InventoryID
> However, when I try to create a view with the same select statement I get
> the following error:
> Msg 4506, Level 16, State 1, Procedure InventoyPublisherView, Line 2
> Column names in each view or function must be unique. Column name 'ID' in
> view or function 'InventoyPublisherView' is specified more than once.
> The CREATE VIEW statement I'm using is:
> CREATE VIEW InventoyPublisherView AS
> (SELECT * FROM Inventory AS I FULL OUTER JOIN Publisher AS P ON
> I.ID=P.InventoryID)
> Many thanks in advance for the help. Very much appreciated
>|||"Chris L" <ChrisL@.discussions.microsoft.com> wrote in message
news:0C10F52D-2845-4F6B-92B7-648A5D1FBC50@.microsoft.com...
> Hi there,
> When I run the following query I get the correct result.
> select * from Inventory As I Full Outer Join Publisher As P on
> I.ID=P.InventoryID
> However, when I try to create a view with the same select statement I get
> the following error:
> Msg 4506, Level 16, State 1, Procedure InventoyPublisherView, Line 2
> Column names in each view or function must be unique. Column name 'ID' in
> view or function 'InventoyPublisherView' is specified more than once.
> The CREATE VIEW statement I'm using is:
> CREATE VIEW InventoyPublisherView AS
> (SELECT * FROM Inventory AS I FULL OUTER JOIN Publisher AS P ON
> I.ID=P.InventoryID)
> Many thanks in advance for the help. Very much appreciated
It's because you have a column named ID in both tables.
If you didn't use Select * (and you should not) you wouldn't have the
problem. Name the columns.
Besides, why would you select I.ID and P.InventoryID in the query since they
have the same value.|||chris,
use column names in the select list instead of the asterisk:
select i.id, i.col2, i.col3, p.col1, p.col2, etc..
from Inventory As I Full Outer Join Publisher As P on I.ID=P.InventoryID
dean
"Chris L" <ChrisL@.discussions.microsoft.com> wrote in message
news:0C10F52D-2845-4F6B-92B7-648A5D1FBC50@.microsoft.com...
> Hi there,
> When I run the following query I get the correct result.
> select * from Inventory As I Full Outer Join Publisher As P on
> I.ID=P.InventoryID
> However, when I try to create a view with the same select statement I get
> the following error:
> Msg 4506, Level 16, State 1, Procedure InventoyPublisherView, Line 2
> Column names in each view or function must be unique. Column name 'ID' in
> view or function 'InventoyPublisherView' is specified more than once.
> The CREATE VIEW statement I'm using is:
> CREATE VIEW InventoyPublisherView AS
> (SELECT * FROM Inventory AS I FULL OUTER JOIN Publisher AS P ON
> I.ID=P.InventoryID)
> Many thanks in advance for the help. Very much appreciated
>|||Thank you very much, Tibor. I am following exercises from a Wrox book. I
think I will have to shoot the author for writing incorrect code in his
examples.
"Tibor Karaszi" wrote:

> Name the columns in the SELECT statement. Both tables has a column named I
D, and the view cannot
> have two columns with the same name.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Chris L" <ChrisL@.discussions.microsoft.com> wrote in message
> news:0C10F52D-2845-4F6B-92B7-648A5D1FBC50@.microsoft.com...
>|||Thank you very much Raymond. The speed of all of your replies (from all of
you guys) is very reassuring for a total beginner like myself. Fantastic job
.
thanks
"Raymond D'Anjou" wrote:

> "Chris L" <ChrisL@.discussions.microsoft.com> wrote in message
> news:0C10F52D-2845-4F6B-92B7-648A5D1FBC50@.microsoft.com...
> It's because you have a column named ID in both tables.
> If you didn't use Select * (and you should not) you wouldn't have the
> problem. Name the columns.
> Besides, why would you select I.ID and P.InventoryID in the query since th
ey
> have the same value.
>
>|||Thanks Dean. Much appreciated. Like I mentioned in the post above the author
of the book I′m following put incorrect code in his examples. Luckily, ther
e
are great people out there to come to the resuce. Cheers
"Dean" wrote:

> chris,
> use column names in the select list instead of the asterisk:
> select i.id, i.col2, i.col3, p.col1, p.col2, etc..
> from Inventory As I Full Outer Join Publisher As P on I.ID=P.InventoryID
> dean
> "Chris L" <ChrisL@.discussions.microsoft.com> wrote in message
> news:0C10F52D-2845-4F6B-92B7-648A5D1FBC50@.microsoft.com...
>
>|||"Chris L" <ChrisL@.discussions.microsoft.com> wrote in message
news:00CEBECA-7183-436E-BB8A-CA36F3388B23@.microsoft.com...
> Thank you very much, Tibor. I am following exercises from a Wrox book. I
> think I will have to shoot the author for writing incorrect code in his
> examples.
This must be why WROX went bankrupt.
All their authors were shot. :-)