query to fetch data from a linked table

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 2008 Forums

Transact-SQL (2008)

query to fetch data from a linked table

Author

Topic

ayadav0984
Starting Member

21 Posts

Posted - 2011-01-25 : 03:13:45

select s.SubOrderID,c.DirectLevelSubCategory7,c.SubCategory6,c.SubCategory5,c.SubCategory4,c.SubCategory3,c.SubCategory2 from SubOrder s,ProductItemMap pim,Product p,ProductCategoryMap pcm,ProductCategory pc ,CategoryMaster c
where
s.SubOrderProductItemID = pim.ProductItemMapID and
pim.ProductItemMapProductId = p.ProductID and
p.ProductID in (select ProductCategoryMapProductID from ProductCategoryMap where
pcm.ProductCategoryMapProductID = pc.ProductCategoryID and
pc.ProductCategoryID = c.ODSCategoryMasterID )
and s.SubOrderID ='30247855' and s.SubOrderProductItemID ='76276' and MONTH(s.SubOrderDate)=8

What is wrong in this query? Since i am getting multiple records for a particular suborder.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2011-01-25 : 03:35:58

i think u can simply write like this
select s.SubOrderID,c.DirectLevelSubCategory7,c.SubCategory6,c.SubCategory5,c.SubCategory4,c.SubCategory3,c.SubCategory2 from SubOrder s,ProductItemMap pim,Product p,ProductCategoryMap pcm,ProductCategory pc ,CategoryMaster c
where
s.SubOrderProductItemID = pim.ProductItemMapID and
pim.ProductItemMapProductId = p.ProductID and
p.ProductID = pcm.ProductCategoryMapProductID
and pcm.ProductCategoryMapProductID = pc.ProductCategoryID and
pc.ProductCategoryID = c.ODSCategoryMasterID
and s.SubOrderID ='30247855' and s.SubOrderProductItemID ='76276' and MONTH(s.SubOrderDate)=8

y u r using in clause in the where condition

ayadav0984
Starting Member

21 Posts

Posted - 2011-01-25 : 03:50:16

i tried doing this way also but then m getting no results ..i think there is some issue with subquery..

ayadav0984
Starting Member

21 Posts

Posted - 2011-01-25 : 05:44:07

Please provide me any other way to write the above query..

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-01-25 : 12:48:26

Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. If you know how, follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.

Sample data is also a good idea, along with clear specifications. It is very hard to debug code when you do not let us see it. If you want to learn how to ask a question on a Newsgroup, look at: http://www.catb.org/~esr/faqs/smart-questions.html

It looks like you have a repeated group in sub-categories. Why are there singular table names? There is no such thing as a "category_id"; an attribute can be a "<something>_id" or "<something>_category" by definition.

I have no idea what a "map" is in RDBMS. But I have seen the term used in an EAV design.

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-26 : 11:45:53

i think the problem is you've one to many relationship between tables. To suggest anything we need to see how data exist in your tables. so as Celko suggested post some sample data

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

ayadav0984
Starting Member

21 Posts

Posted - 2011-01-29 : 06:41:02

yes visakh you are right ..there is problem in the relationship between tables ..anywayz thanks guys for your help

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources