inner join problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

inner join problem

Author

Topic

packymyles
Starting Member

21 Posts

Posted - 2008-04-23 : 16:27:50

select product.serial_number, model.model_number, model.model_name,
part_inventory.part_number, model_configuration.Where_the_Part_is_Used, model_configuration.Quantity_of_Part_Used
from model inner join model_configuration on
model.model_number = model_configuration.model_number
inner join part_inventory on
model_configuration.part_number = part_inventory.part_number
inner join product on
model.serial_number = product.serial_number
where product.serial_number = 1;

when i execute this i get Msg 207, Level 16, State 1, Line 9
Invalid column name 'serial_number'.
it's referring to

model.serial_number = product.serial_number

serial_number is the PK of product, but is a foreign key for model, so i dont know why it wont work. but i'm also pretty new to sql...lol

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-23 : 16:36:26

Post the CREATE TABLE statements for both model and product.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

packymyles
Starting Member

21 Posts

Posted - 2008-04-23 : 16:56:37

Create table Model(
Model_Number char(10) Primary key not null,
Serial_Number char (10) not null,
Model_Name varchar (20) not null,
Product_Line varchar (20) not null,
Size_ varchar (30) not null,
Introduction_Date smalldatetime not null,
Discontinuation_Date smalldatetime not null,
Foreign Key (Serial_Number) references Product (Serial_Number)
);

Create table Product(
Serial_Number char (10) Primary key not null,
Order_Serial_Number char (10) not null,
Price dec (5,2) not null,
Delievery_Date smalldatetime not null,
Shipping_Instructions varchar (50) not null,
Color_of_Bike varchar (15) not null,
Bike_Size varchar (10) not null,
Parts_Used_in_Assembly_Process varchar (20) not null,
Foreign Key (Order_Serial_Number) references Order_ (Order_Serial_Number)
);

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-23 : 17:01:21

It looks fine to me, can't figure out why you are getting that error.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

packymyles
Starting Member

21 Posts

Posted - 2008-04-23 : 17:06:02

then maybe the data i inserted into the tables caused the problem?? not sure?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-23 : 17:11:40

Data would not impact this.

Just thought of something. Try using Serial_Number in your query instead of serial_number. There can be a difference, depends on your collation.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

packymyles
Starting Member

21 Posts

Posted - 2008-04-23 : 17:12:52

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '='.

that's what i get when i parse the line of code that the error was pointing to

packymyles
Starting Member

21 Posts

Posted - 2008-04-23 : 17:15:39

that doesn't seem to change anything

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-23 : 17:16:13

You can't parse just that one line. You must parse an entire query.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

packymyles
Starting Member

21 Posts

Posted - 2008-04-23 : 17:19:03

when i parse the entire query, it completes succesfully, and when i just execute the query, it has that same old error, is this a good thing?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-23 : 17:23:50

The query is syntactically correct. You are getting the error at runtime.

Run just this and let us know what happens:
select product.serial_number
from model
inner join product
on model.serial_number = product.serial_number
where product.serial_number = 1

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

packymyles
Starting Member

21 Posts

Posted - 2008-04-23 : 17:32:47

Msg 207, Level 16, State 1, Line 4
Invalid column name 'serial_number'.

same error from before huh?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-23 : 17:40:33

Now try this:

select serial_number from product
select serial_number from model

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

packymyles
Starting Member

21 Posts

Posted - 2008-04-23 : 17:48:26

Msg 207, Level 16, State 1, Line 3
Invalid column name 'serial_number'.

same thing happened with executing those 2 lines from before

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-23 : 18:14:33

Do you get the error twice or once?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

packymyles
Starting Member

21 Posts

Posted - 2008-04-23 : 18:17:46

only once

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-23 : 18:19:11

Run each query separately to determine which one errored. Whichever one that is, the column is not named serial_number in that table.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-23 : 18:22:22

Copy/paste the results of the following query into this thread:

SELECT TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME IN ('model', 'product')

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

packymyles
Starting Member

21 Posts

Posted - 2008-04-23 : 18:27:29

Product Serial_Number
Product Order_Serial_Number
Product Price
Product Delievery_Date
Product Shipping_Instructions
Product Color_of_Bike
Product Bike_Size
Product Parts_Used_in_Assembly_Process
Model Model_Number
Model Model_Name
Model Product_Line
Model Size_
Model Introduction_Date
Model Discontinuation_Date

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-23 : 18:29:05

Model does not contain Serial_Number!!!

Next time you show us the CREATE TABLE statement, make sure that you generate it from Management Studio rather than by hand. It would have saved me quite a bit of time.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-04-23 : 18:31:17

That's funny. When SQL is there to script out table, why you have to do by hand..

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources