How to select with ID ? - 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

SQL Server 2005 Forums

Transact-SQL (2005)

How to select with ID ?

Author

Topic

headshot
Starting Member

9 Posts

Posted - 2012-04-21 : 12:47:19

Hello every body.
I have a database with two table Product and DetailBill
Table Product
IDPro NamePro Descrypt ImagePro

Table DetailBill
IDBill IDPro Count Price

I have a procedure


ALTER proc [dbo].[sp_Product_2]
as	
select  top(9) IDPro,count(IDPro) as SumofPro
 from DetailBill
group by IDPro
order by SumofPro desc

I must select * from Product with IDPro bellow , but i can`t .
Can you help me ?
I try


ALTER proc [dbo].[sp_Product_2]
as	
Select * from Product where IDPro in (select  top(9) IDPro,count(IDPro) as SumofPro
 from DetailBill
group by IDPro
order by SumofPro desc)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-21 : 12:51:37

the count() within subquery doesnt make sense. it should be


ALTER proc [dbo].[sp_Product_2]
as	
Select * from Product 
where IDPro in (select  top(9) IDPro
 from DetailBill
group by IDPro
order by count(IDPro) desc)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

headshot
Starting Member

9 Posts

Posted - 2012-04-21 : 12:58:54

Thank for visakh16.
But you can tell me why not use "count(IDPro) as SumofPro"
I want to create one column in table then query and all column in Product, It`s name is SumofPro

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-21 : 13:18:37

you cant have more than a single column in your subquery if you're linking it to main query using IN. If you really want to use count inside then you need to use join instead. like


SELECT p.*
FROM Product p
INNER JOIN (select  top(9) IDPro,count(IDPro) as SumofPro
 from DetailBill
group by IDPro
order by SumofPro desc)p1
ON p1.IDPro = pIDPro

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

headshot
Starting Member

9 Posts

Posted - 2012-04-22 : 06:02:04

Dear visakh16.
I think this is enough ,but anyway, thank you very much .


ALTER proc [dbo].[sp_Product_2]
as	
Select * from Product 
where IDPro in (select  top(9) IDPro
 from DetailBill
group by IDPro
order by count(IDPro) desc)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-22 : 13:28:52

quote:
Originally posted by headshot

Dear visakh16.
I think this is enough ,but anyway, thank you very much .

ALTER proc [dbo].[sp_Product_2]
as
Select * from Product
where IDPro in (select top(9) IDPro
from DetailBill
group by IDPro
order by count(IDPro) desc)

yep...thats enough

I was just telling you how to use query in join
so far as you dont need to return count value you dont need it in select list

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources