Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 full text search-query
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

purisqlserver
Yak Posting Veteran

India
73 Posts

Posted - 08/18/2001 :  05:26:17  Show Profile  Reply with Quote
I am executing a query,which is returning duplicate records.
The resultset should consist of unique records.
The table structure and the data is as follow:

I have three table Group_Detail,Item_Master,Group_Feature.

GroupDetail
Group_Code(pk)
G0001
G0002
G0003


Item_Master
Group_Code(fk)
G0001
G0001
G0002

Group_Feature
Group_Code(fk)
G0001
G0001
G0002


The query in question:

Select distinct(G.Group_Code),G.Group_Title,G.Group_Main_Cat,
G.Group_Sub1,G.Group_Sub2,G.Group_Sub3,G.Group_Specification,
G.Group_Description,I.Item_Number,F.Group_Feature
from Group_Detail G,Item_Master I,Group_Feature F
where G.Group_Code=I.Group_Code and
G.Group_Code=F.Group_Code
and (contains(G.*,'quality')
or contains(I.*,'quality'))

I am using full text search to search for particular string
in all the three tables,columns included in the full index.
Thank you.


nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 08/18/2001 :  09:05:07  Show Profile  Visit nr's Homepage  Reply with Quote
Guess you have two features for G0001 and you want to display the Group_Feature's - if you want a single rec for this then you need to decide which Group_Feature to return or return a list. Same for ItemMaster.


==========================================
Cursors are useful if you don't know sql.
Go to Top of Page

purisqlserver
Yak Posting Veteran

India
73 Posts

Posted - 08/18/2001 :  09:51:22  Show Profile  Reply with Quote
The group_Feature table consists of two
columns-Group_Code and Group_Feature.
The data is of-
G0001 Feature1
G0001 Feature2
G0002 Feature21
etc.
"Is there any other better way of doing this"
I am using this to search for a particular string in selected columns from different tables in a database thro' ASP.
Thank you

Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 08/18/2001 :  10:12:11  Show Profile  Visit nr's Homepage  Reply with Quote
If you only want one row per group_code which of these two Group_Feature do you want for G0001?

You could use
Select G.Group_Code,G.Group_Title,G.Group_Main_Cat,
G.Group_Sub1,G.Group_Sub2,G.Group_Sub3,G.Group_Specification,
G.Group_Description,min(I.Item_Number),min(F.Group_Feature)
from
where
group by G.Group_Code,G.Group_Title,G.Group_Main_Cat,
G.Group_Sub1,G.Group_Sub2,G.Group_Sub3,G.Group_Specification,
G.Group_Description



==========================================
Cursors are useful if you don't know sql.
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000