SSMS Express: Creating Parent-Child Table via LEFT OUTER JOIN - Error Messages 8152, 207 & 4104

• S. H. Chang

  Old Hand

  Points: 349

  More actions

  May 23, 2006 at 8:11 am

  #172576

  Hi all,

  I got Error Messages 8152, 207 and 4104 when I executed the following T-SQL code in my SQL Server Management Studio Express:

  /////--SQLQueryParentChild.sql---////////

  Use newDB

  GO

  ----Creating dbo.Person as a Parent Table----

  CREATE TABLE dbo.Person

  (PersonID int PRIMARY KEY NOT NULL,

  FirstName varchar(25) NOT NULL,

  LastName varchar(25) NOT NULL,

  City varchar(25) NOT NULL,

  State varchar(25) NOT NULL,

  Phone varchar(25) NOT NULL)

  INSERT dbo.Person (PersonID, FirstName, LastName, City, State, Phone)

  SELECT 1, 'George', 'Washington', 'Washington', 'DC', '1-000-1234567'

  UNION ALL

  SELECT 2, 'Abe', 'Lincoln', 'Chicago', 'IL', '1-111-2223333'

  UNION ALL

  SELECT 3, 'Thomas', 'Jefferson', 'Charlottesville', 'VA', '1-222-4445555'

  GO

  ----Creating dbo.Book as a Child table----

  CREATE TABLE dbo.Book

  (BookID int PRIMARY KEY NOT NULL,

  BookTitle varchar(25) NOT NULL,

  AuthorID int NOT NULL FOREIGN KEY REFERENCES Person(PersonID)

  )

  INSERT dbo.Book (BookID, BookTitle, AuthorID)

  SELECT 1, 'How to Chop a Cherry Tree', 1

  UNION ALL

  SELECT 2, 'Valley Forge Snow Angels', 1

  UNION ALL

  SELECT 3, 'Marsha and ME', 1

  UNION ALL

  SELECT 4, 'Summer Job Surveying Viginia', 1

  UNION ALL

  SELECT 5, 'Log Chopping in Illinois', 2

  UNION ALL

  SELECT 6, 'Registry of Visitors to the White House', 2

  UNION ALL

  SELECT 7, 'My Favorite Inventions', 3

  UNION ALL

  SELECT 8, 'More Favorite Inventions', 3

  UNION ALL

  SELECT 9, 'Inventions for Which the World is Not Ready', 3

  UNION ALL

  SELECT 10, 'The Path to the White House', 2

  UNION ALL

  SELECT 11, 'Why I Do not Believe in Polls', 2

  UNION ALL

  SELECT 12, 'Doing the Right Thing is Hard', 2

  GO

  ---Try to obtain the LEFT OUTER JOIN Results for the Parent-Child Table

  SELECT * FROM Person AS I LEFT OUTER JOIN Book ON I.ID=P.ID

  GO

  //////--Messages--/////////

  (3 row(s) affected)

  Msg 8152, Level 16, State 14, Line 7

  String or binary data would be truncated.

  The statement has been terminated.

  Msg 207, Level 16, State 1, Line 2

  Invalid column name 'ID'.

  Msg 4104, Level 16, State 1, Line 2

  The multi-part identifier "P.ID" could not be bound.

  ////////////////////////////////////////////////////////

  I found the Table "Person" is completely  filled with 3 rows of input data and the Table "Book" is only filled with the titles and no input data.  Please help, tell me where I made mistakes and advise me how to correct the problems.

  Thanks in advance,

  Scott  Chang  

• S. H. Chang

  Old Hand

  Points: 349

  More actions

  May 24, 2006 at 9:31 am

  #639832

  UPDATE:

  I changed "BookTitle varchar(25) NOT NULL," to "BookTitle varchar(50) NOT NULL," and I created 2 tables (Person and Book) sucessfully and saved them in the database newDB. I executed the following code:

  ///--ParentChild.sql--///

  USE newDB

  SELECT * FROM Person AS P LEFT OUTER JOIN Book AS b ON b.PersonID=P.AuthorID

  GO

   

  I got the following 2 errors:

  ///--Messages--/// 

  Msg 207, Level 16, State 1, Line 2

  Invalid column name 'PersonID'.

  Msg 207, Level 16, State 1, Line 2

  Invalid column name 'AuthorID'.

• Thomas M. Burgan

  SSC Veteran

  Points: 201

  More actions

  May 24, 2006 at 10:05 am

  #639847

  Your SQL should be:

  USE newDB

  SELECT * FROM Person AS P LEFT OUTER JOIN Book AS b ON P.PersonID=b.AuthorID

  GO

• S. H. Chang

  Old Hand

  Points: 349

  More actions

  May 24, 2006 at 10:38 am

  #639854

  Hi Thomas,  Thanks for your response.

  The revised code you suggested works like a charm. Wonderful!!!

  Thanks again,

  Scott Chang