How do I search multiple tables using one variable in SQL?

Question

My pride doesn't like me asking this... But I'm getting desperate. I 'm trying to create a stored procedure that will search multiple tables using one variable (orderid, located in all tables). The code parses ok, but on execute, I get the message "Ambiguous column name 'orderid' ".

My code looks like this:

CREATE PROCEDURE sp_Get_Order_Items
@orderid int
AS
SELECT OrderItems.name, OrderItems.quantity, OrderItems.notes, ItemStatus.statusid

FROM OrderItems JOIN ItemStatus
ON OrderItems.orderid = ItemStatus.orderid
WHERE orderid = @orderid

Can anyone give advice? Or recommend a website? Thanks!

Answer 1

Yes - where you have "orderid" written by it's self you must add tbl_name.orderid - where tbl_name = the name of the table from which you want to specify a orderid column....

Since orderid exists in multiple tables mssql doesn't know which column by that name you are referring.

Its almost perfect -checkit ---


SELECT OrderItems.name, OrderItems.quantity, OrderItems.notes, ItemStatus.statusid

FROM OrderItems JOIN ItemStatus
ON OrderItems.orderid = ItemStatus.orderid
WHERE OrderItems.orderid = @orderid

Answer 2

Since orderid is a column found in all these tables, it's not clear which table's orderid you want to compare @orderid to. Just consider the case that the columns contained different information, but just happened to have the same column name. You would want to specify which table's orderid column you wanted to compare @orderid to.

In your case, it seems you are implying that orderid is the same information in all the tables, so it doesn't really matter which one you use. Just replace your "orderid" with "OrderItems.orderid" or "ItemStatus.orderid". I'm *pretty* sure either will give you the same result.

Answer 3

Whenever I use SQL, i make the query in Access, then right click where field names are, and click SQL.

Then when in the programming software, change the direct numbers to variables and you never go wrong. Its much easier and quick than typing it out and testing why its not working. Espeically when using more than one table.

Answer 4

that's a go-web site scripting vulnerability. It shows an sq. server that is both no longer set up good and/or does no longer have each and each of the Microsoft safe practices updates utilized. till you get the safe practices themes fixed, do not complication cleansing it, it receives re-contaminated. it really is an same form of hack that at present made the archives with Obama's internet web site being hacked.