Select Query() - Doubling ...? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

General SQL Server Forums

New to SQL Server Programming

Select Query() - Doubling ...?

Author

Topic

paramu
Posting Yak Master

151 Posts

Posted - 2011-12-05 : 03:39:06

Hi, Iam using SQLServer 2008. When I use the below Query its showing the doubling results.

Select vsr.*,wi.material_name,j.spareparts_name from Material_Stock..materialmst vsr, Material_Stock..goodslist wi, Spares_Tools..toolslist j where vsr.inward_no='5' and wi.material_code=vsr.material _code and j.tools_id=vsr.tools_id order by(0+inward_no) desc,sl_no

Also when I use the below query its good

Select vsr.*,wi.material_name from Material_Stock..materialmst vsr, Material_Stock..goodslist wi where vsr.inward_no='5' and wi.material_code=vsr.material _code order by(0+inward_no) desc,sl_no

But I want to select from three tables like the first query...For that any idesas will be welcome.
Thanks for the ideas...

Paramu @ PARANTHAMAN

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-05 : 03:55:53

if you want to make them single records how will you show multiple values from Spares_Tools..toolslist table?
As the relationship is one to many you can show either only one of values or show all related values as a comma seperated list. Can you tell what you're expecting as output?

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

paramu
Posting Yak Master

151 Posts

Posted - 2011-12-05 : 04:08:27

Hi Visakh, Thanks. Yes. That relation is one to many and I would like to show only one ...Is it possible?

Thanks Again

Paramu @ PARANTHAMAN

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-05 : 04:15:53

only one means which one...min one,max one or random one?

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

paramu
Posting Yak Master

151 Posts

Posted - 2011-12-05 : 04:37:52

Actually tools_id is string and contains the same value for multi rows like the below..

====================================================
goods_name, spareparts_name, tools_id
====================================================
3 inches round spanner, spanner, daily_tool1
4 inches round spanner, spanner, daily_tool1

If my materialmst is having "tools_id=daily tool1" then it should give the spareparts_name as "spanner"

Thanks

Paramu @ PARANTHAMAN

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-05 : 05:06:04

then use like


Select vsr.*,wi.material_name,j.spareparts_name 
from Material_Stock..materialmst vsr
inner join Material_Stock..goodslist wi
on wi.material_code=vsr.material _code
inner join (
select tools_id,max(spareparts_name) as spareparts_name
from Spares_Tools..toolslist 
group by tools_id
)j 
on j.tools_id=vsr.tools_id 
where vsr.inward_no='5' 
order by(0+inward_no) desc,sl_no

and learn to use ANSI join syntax as shown above for better code clarity

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

paramu
Posting Yak Master

151 Posts

Posted - 2011-12-05 : 05:13:13

Thats what I need....Thanks a lot....for great helps....

Paramu @ PARANTHAMAN

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-05 : 05:15:46

wc

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources