SSMS Express: Creating Parent-Child Table via LEFT OUTER JOIN - Error Message 156

SSMS Express: Creating Parent-Child Table via LEFT OUTER JOIN - Error Message 156

• Elizabeth Maher MSFT

  Hi Umachandar, Thanks for your response.

  I corrected my code as you instructed. I executed my revised code (see the listed code below) and I got the 2 tables "Person" and "Book" as well as a new error 4104:

  Msg 4104, Level 16, State 1, Line 2

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

  I think I have a problem with the Foreign Key. Please help and advise how I can correct it.

  Thanks again,

  Scott Chang

  /////////--revised---SQLqueryPerson&Book.sql---////////////////

  Use newDB

  --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 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 Book as a Child table----

  CREATE TABLE Book

  (BookID int PRIMARY KEY NOT NULL,

  BookTitle varchar(50) NOT NULL,

  AuthorID int NOT NULL FOREIGN KEY REFERENCES Person(PersonID)

  )

  INSERT 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.PersonID=P.AuthorID

  GO

  ///////////////////--Results--/////////////////////

  (3 row(s) affected)

  (12 row(s) affected)

  Msg 4104, Level 16, State 1, Line 2

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

  
  
  

• BlokHead

  Hi Mark, Thanks for your response.

  I corrected everything as you instructed. I executed the revised code and I got the following results and error 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 have no ideas how to make changes on the revised code. Please help and advise again.

  Thanks again,

  Scott Chang

  
  
  

• pmukherj

  The error 8152 indicates that some of the values in the INSERT statement exceed the length of the column. So check the length of the strings to make sure they are less than or equal to the varchar column. The error 207 indicates that you have an invalid column in the statement so you will have to check each statement (usually you can double-click on the error in SSMS and you will be positioned on the line that raised this error). The error 4104 indicates that the column specification is incorrect. Check the table that is referred to by the alias "P" to make sure it contains a column called "ID" or change the alias to the appropriate table that contains it.
  
  

• Vasco Marques

  The SELECT statement below has several problems:

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

  You have an alias P but no table in the SELECT statement has the alias assigned to it. See Books Online for more details on syntax of SELECT statement and how to specify/use table aliases. The correct syntax for the SELECT statement above is:

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

  
  
  

• BradN

  
  Change the CREATE TABLE statement for Book to

  
  CREATE TABLE dbo.Book
  (BookID int PRIMARY KEY NOT NULL,
  BookTitle varchar(25) NOT NULL,
  AuthorID int NOT NULL FOREIGN KEY REFERENCES Person(PersonID)
  )

  
  I would also suggest changing the double quotes to single quotes
  throughout
  

  
  

• Domino

  It looks like you have the aliases wrong for the columns. Based on the names of your tables, it should be P.PersonID and b.AuthorID. So the SELECT statement should be:

  USE newDB

  SELECT * FROM Person AS P

  LEFT OUTER JOIN Book AS b

  ON P.PersonID=b.AuthorID

  GO

  The alias is like a nick name or shortcut that can be used to reference the table and it should match the ones that have the columns.

  
  
  

• Wrangler

  In addition to the error messages 8152, 207 and 4104, 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. I think the "Foreign Key...REFERENCES Person(PersonID)" is not valid. Please help, tell me where I made mistakes and advise me how to correct the problems.

  Thanks, Scott Chang

  
  
  

• Visualcpp

  Hello Umachandar, Thanks for your response.

  I have 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'.

  I looked up the Books Online for more details on syntax of SELECT statement and how to specify/use table aliases and I could not find the materils you mentioned (Sorry! I am a beginner in doing T-SQL, SQL Server Express and SQL Server Management Studio Express). Please help and advise me how to resolve this problem.

  Thanks,

  Scott Chang

  
  
  
• SSMS Express: Creating Parent-Child Table via LEFT OUTER JOIN - Error Message 156