How to select columns from two tables sql server

Question

I need to select three columns from two different tables in sql server 2008. i tried below query but its show error like this

error message

Column 'tb_new_product_Name_id.Product_Name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

query

select pn.Product_Name as [Product Name], pn.Product_Id as [Product Id],COUNT(pnd.Product_id)+1 as duplicate_id 
from tb_new_product_Name_id as pn,tb_new_product_Name_id_duplicate as pnd 
where  pn.Product_Name LIKE '%'+@product_name_id+'%' 
or (pn.Product_Id like '%'+@product_name_id+'%' and pnd.Product_Id like '%'+@product_name_id+'%' );

where i made mistake ?

score 1 · Accepted Answer · edited May 23 '17 at 11:50

If you're going to have a count in your select statement you have to group on the other columns

select pn.Product_Name as [Product Name], pn.Product_Id as [Product Id],COUNT(pnd.Product_id)+1 as duplicate_id 
from tb_new_product_Name_id as pn
  ,tb_new_product_Name_id_duplicate as pnd 
where  pn.Product_Name LIKE '%'+@product_name_id+'%' 
  or (pn.Product_Id like '%'+@product_name_id+'%' and pnd.Product_Id like '%'+@product_name_id+'%' );
group by pn.Product_name, pn.Product_ID

You should also look at using explicit join syntax

score 1 · Answer 2 · answered Dec 26 '13 at 13:57

You're using aggregate function COUNT, so you need to group by the other column that are not part in the aggregate.

Try this:

select pn.Product_Name as [Product Name], pn.Product_Id as [Product Id],COUNT(pnd.Product_id)+1 as duplicate_id from tb_new_product_Name_id as pn,tb_new_product_Name_id_duplicate as pnd 
where  pn.Product_Name LIKE '%'+@product_name_id+'%' or (pn.Product_Id like '%'+@product_name_id+'%' and pnd.Product_Id like '%'+@product_name_id+'%' )
group by pn.Product_Name, pn.Product_Id;

score 1 · Answer 3 · answered Dec 26 '13 at 13:57

You need to use GROUPBY when you are selecting any columns with Aggregate functions like count.

Try the following query:

  select pn.Product_Name as [Product Name], pn.Product_Id as [Product Id],COUNT(pnd.Product_id)+1 as duplicate_id from tb_new_product_Name_id as pn,tb_new_product_Name_id_duplicate as pnd where  pn.Product_Name LIKE '%'+@product_name_id+'%' or (pn.Product_Id like '%'+@product_name_id+'%' and pnd.Product_Id like '%'+@product_name_id+'%' ) Group by pn.productname,pn.ProductID

Hope it helps..

How to select columns from two tables sql server

3 Answers3