locked
My SQL query procedure will not execute when join more than one table? RRS feed

  • Question

  • User1909155429 posted
      SELECT [ProductID],Products.ProductDescription,[VendorName], Products.CategoryID,categoryid_p_items,Product_items.categoryid
    	     FROM 
       (SELECT [ProductID],Products.ProductDescription,[VendorName], Products.CategoryID,categoryid_p_items,Product_items.categoryid,
    			ROW_NUMBER() OVER(ORDER BY  @sortExpression ) as RowNum  
    			FROM Products LEFT JOIN Product_items ON Products.categoryid_p_items = Product_items.categoryid where Discontinued <> 'True' )AS EmpInfo 

    Column categoryid was specified multiple times for EmpInfo. The multipart identifier could not be bound:Products.CategoryID,Products.ProductDescription

    Tuesday, September 22, 2020 1:20 PM

All replies

  • User475983607 posted

    When asking for query help on the forum, we need the actual data, schema, and parameter values.  Typically, folks will share script that creates and populates temp tables that represents the actual data.  Otherwise, the community has to guess.

    I assume the filter causes the empty result set.  Try removing the WHERE and the parameter.

    SELECT [productid], 
           products.productdescription, 
           [vendorname], 
           products.categoryid, 
           categoryid_p_items, 
           product_items.categoryid
    FROM   products 
           LEFT JOIN product_items 
                  ON products.categoryid_p_items = product_items.categoryid 

    Tuesday, September 22, 2020 2:25 PM
  • User1909155429 posted

    I guess i would require Management server to do that? and if i knew all about that i would not rely on expertise!

    I have narrow the problem down from original dynamic script, to a design configuration.The only thing missing is the row number at the bottom.

    I presumed looking at the code you would be able to spot the problem quickly? something to do with the identifier? I dont know how to identify two different table data using syntax *

    Thanks

    Wednesday, September 23, 2020 6:40 PM
  • User-939850651 posted

    Hi peterthegreat,

    According to your description, I tested the query statement you provided, and it did have such a problem.

    First of all, I found that the query columns in the statement is the same as the columns in the subquery, so you could try not to use the subquery.

    Second, if you must use a subquery, then the column names you need to get should come from the dataset of the subquery, which is EmpInfo.

    Please try this query:

    SELECT	[ProductID],
    		EmpInfo.ProductDescription,
    		[VendorName], 
    		EmpInfo.CategoryID,
    		categoryid_p_items,
    		EmpInfo.categoryid
    	FROM  (
    			SELECT [ProductID],
    			Products.ProductDescription,
    			[VendorName], 
    			Products.CategoryID,
    			categoryid_p_items,
    			Product_items.categoryid,
    			ROW_NUMBER() OVER( ORDER BY @sortExpression) as RowNum  
    				FROM Products 
    			LEFT JOIN Product_items 
    				ON Products.categoryid_p_items = Product_items.categoryid 
    				WHERE Discontinued <> 'True' 
    	      ) AS EmpInfo

    Note: I am not sure what @sortExpression defines, I used Products.[ProductID] for testing.

    If I misunderstood something, please feel free let me know.

    Best regards,

    Xudong Peng

    Thursday, September 24, 2020 8:48 AM
  • User1909155429 posted

    The problem with the solution is that both Products and Product_items have the same categoryid column names?  so would produce an error.

    the sortexpression is related to extra script not included and did not seem relevant while testing for errors.

    I have multiple queries in procedure  showing same code!! except for where clause. I am hoping to remove all occurances and just execute one statement at bottom,later.

    Thursday, September 24, 2020 4:22 PM
  • User753101303 posted

    Hi,

    With

    ON Products.categoryid_p_items = Product_items.categoryid 

    it seems you asked that they have the same category. Is this your real intent or do you have a products made of several product items???

    As pointed already the source issue is that you are using a subquery using tables and exposing the resultset using an alias. Then rather than using this alias you try to use table names coming from the subquery which are here "out of scope" :

    SELECT databases.name -- The multi-part identifier "databases.name" could not be bound (as databases is now out of scope)
    FROM
    (
    	SELECT name FROM sys.databases
    ) AS test
    
    SELECT test.name -- works
    FROM
    (
    	SELECT name FROM sys.databases
    ) AS test

    Try perhaps to fix this as much as you can and then tell which problem you have now. If not a failure but a bad results it seems to me that joining products and product items by using their category looks weird. Seems it woould be rather a product id ????

    Edit: if you have multiple columns with the same name you HAVE to use the the table alias (ie myTable.MyColumn) and give them an alias so that can expose the final result using a unique column name.

    Thursday, September 24, 2020 4:39 PM
  • User475983607 posted

    The problem with the solution is that both Products and Product_items have the same categoryid column names?  so would produce an error.

    the sortexpression is related to extra script not included and did not seem relevant while testing for errors.

    I have multiple queries in procedure  showing same code!! except for where clause. I am hoping to remove all occurances and just execute one statement at bottom,later.

    This is why the community asks for the table schema and sample data. 

    Trust me, the community can clearly see the poorly designed code that contains an unknown parameter.  If you provide the schema, sample data, and parameter, then the community can provide an accurate solution.  We can also explain any holes in your understanding as well as provide recommendations.

    Thursday, September 24, 2020 4:44 PM
  • User1909155429 posted

    Nothing worked except  select *

    Yes the categoryid returns several items. I should make amendments with unique names i suppose but that would cause a major disruption.

    I think what the procedure does is return result set with row counter than sends that to the outer query which filters on specific row numbers passing in start row and max row parameters to return one page sub set at a time.

    Thanks

    Saturday, September 26, 2020 7:18 PM
  • User475983607 posted

    It's difficult to provide a solution without the schema, sample data, and an example of the output.   Please make an effort...

    Saturday, September 26, 2020 8:06 PM