Ambiguous column name? - 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

Ambiguous column name?

Author

Topic

argon007
Starting Member

38 Posts

Posted - 2008-05-30 : 23:50:53

....
nothing...please see the below...

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-30 : 23:53:37

There is nothing wrong with what you have posted. It works fine in both SQL Server 2000 and 2005.

So could you post your actual query rather than a simplified one?

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

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

argon007
Starting Member

38 Posts

Posted - 2008-05-31 : 00:07:58

[code]SELECT order_id, quantity
FROM order_details
UNION
SELECT product_id, reorder_level
FROM products
UNION
SELECT supplier_id
FROM suppliers[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-31 : 00:35:43

quote:

Originally posted by argon007

SELECT order_id, quantity
FROM order_details
UNION
SELECT product_id, reorder_level
FROM products
UNION
SELECT supplier_id,somecolumnhere
FROM suppliers

You should have same number & same datatype columns returned from each batch of code seperated by UNION/UNION ALL. So add a second column to last batch which is of same datatype as reorder_level,quantity. If you dont wish to return any more columns in last batch then put a NULL or hardcoded value.

argon007
Starting Member

38 Posts

Posted - 2008-05-31 : 00:46:06

Thanks, but that is not I want because it will be like

order_id quantity
----------- -----------
1 NULL
.....

I want that....

order_id quantity product_id reorder_level supplier_id
----------- ----------- -------------- ----------------- -----------
10009 70 23 25 9
....

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-31 : 00:49:18

quote:
Originally posted by argon007

Thanks, but that is not I want because it will be like

order_id quantity
----------- -----------
1 NULL
.....

I want that....

order_id quantity product_id reorder_level supplier_id
----------- ----------- -------------- ----------------- -----------
10009 70 23 25 9
....

You cant get all those fields into a row using UNION. UNION will each of the query batches as seperate rows so that output will be like

10009 NULL
70 23
25 9 ..

If you want them in a row you need to have some relationship b/w the tables. Can you post what are fields on which tables are related?

argon007
Starting Member

38 Posts

Posted - 2008-05-31 : 00:58:55

okay......
now i tried...

from assignment:

quote:
the order id and quantity from the order_details table, the product id and reorder level from the products table, and the supplier id from the suppliers table

In here, I have tried.

SELECT order_id, 
			quantity,
			product_id,
			reorder_level,
			supplier_id
FROM order_details, products, suppliers

the errors:

quote:
Msg 209, Level 16, State 1, Line 5
Ambiguous column name 'product_id'.
Msg 209, Level 16, State 1, Line 7
Ambiguous column name 'supplier_id'.

but if i remove the product_id and suppliers_id, they were okay.

what should i do with keeping product_id and supplier_id?

like this

quote:
order_id quantity product_id reorder_level supplier_id

thank you so much.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-31 : 07:51:22

quote:
Originally posted by argon007

okay......
now i tried...

from assignment:
quote:
the order id and quantity from the order_details table, the product id and reorder level from the products table, and the supplier id from the suppliers table

In here, I have tried.
SELECT order_id,
quantity,
product_id,
reorder_level,
supplier_id
FROM order_details, products, suppliers

the errors:

quote:
Msg 209, Level 16, State 1, Line 5
Ambiguous column name 'product_id'.
Msg 209, Level 16, State 1, Line 7
Ambiguous column name 'supplier_id'.

but if i remove the product_id and suppliers_id, they were okay.

what should i do with keeping product_id and supplier_id?

like this

quote:
order_id quantity product_id reorder_level supplier_id

thank you so much.

You have not specified what is the relationships b/w tables. You need to link them using related column to get the information for each row.
Can you provide structures of order_details,products & suppliers table with some sample data.

argon007
Starting Member

38 Posts

Posted - 2008-05-31 : 14:39:09

in products table...primary key of the product_id, foreign key of supplier_id in supplier table.
in order_detail table...foreign key of the product_id in product table.
in suppliers table... primary key of the supplier_id...

that is all information that i provided.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-31 : 14:53:22

quote:
Originally posted by argon007

in products table...primary key of the product_id, foreign key of supplier_id in supplier table.
in order_detail table...foreign key of the product_id in product table.
in suppliers table... primary key of the supplier_id...

that is all information that i provided.

SELECT od.order_id,od.quantity,p.product_id, p.reorder_level,s.supplier_id
FROM order_details od
INNER JOIN product p
ON p.product_id=p.product_id
INNER JOIN suppliers s
ON s.supplier_id=p.supplier_id

argon007
Starting Member

38 Posts

Posted - 2008-05-31 : 14:56:46

thank you. i will try it.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-05-31 : 23:37:14

Argon007, I know you will be locked down if you delete your post and edit after you get your answer.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-01 : 02:49:27

I don't see the need to lock his account since he edited his post to direct us to his next post which provided the correct query.

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

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources