sql query not working correctly

Question

0.00/5 (No votes)

See more:

USE [cms]
GO
/****** Object:  StoredProcedure [dbo].[SpCateProductsInfo]    Script Date: 10/14/2012 00:50:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[SpCateProductsInfo](@cate_id int)
as
begin
select sb.subcate_name, sum(p.qty) as totalqty, 
(select top 1 pimg_mid1 from product join subcategory
on product.subcate_id=
(
	select top 1 subcate_id
	from subcategory
	where cate_id=@cate_id
	order by NEWID()
)
order by NEWID()) as pimg_mid1
from subcategory sb 
  join product p on p.subcate_id = sb.subcate_id
where sb.cate_id = @cate_id
group by sb.subcate_name


end

it is working f9, But the problem is that the pimg_mid1 column contains same entries (every row regarding the pimg_mid1 column contains same data)

can any 1 tell me abt this error and the solution ???

Posted 14-Oct-12 10:08am

ashokdamani

Add a Solution

Comments

Maciej Los 14-Oct-12 17:12pm

Please, provide more informations. We can't read in your mind...

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Kuthuparakkal · Answer 1 · 2012-10-14T14:56:00

Solution 1

Your query is logically wrong:

If you need all pimg_mid1

SQL

select sb.subcate_name, sum(p.qty) as totalqty,
p.pimg_mid1
from subcategory sb
join product p on p.subcate_id = sb.subcate_id
where sb.cate_id = @cate_id
group by sb.subcate_name, p.pimg_mid1

If you need one(latest) - use Max

SQL

select sb.subcate_name, sum(p.qty) as totalqty,
MAX(p.pimg_mid1) AS pimg_mid1
from subcategory sb
join product p on p.subcate_id = sb.subcate_id
where sb.cate_id = @cate_id
group by sb.subcate_name

Posted 14-Oct-12 14:56pm

Kuthuparakkal

Comments

ridoy 15-Oct-12 0:34am

+5

Herman<T>.Instance 15-Oct-12 4:40am

I always leave MAX at home. It uses tablescan. Better create a desc index on pimg_mid1. Select top 1 pinmg_midl is than always the latest which can be not the maximum.
Furthermore the pimg_midl is chosen by ordering om NewID() so he uses a random pimg_midl