Search Query Help With Duplicates?

Sep 7, 2006

Hello everyone,

Obviously, I'm new here so I'd like to start by thanking anyone for their help with this very newbie SQL search query question.

I have been tasked with trying to figure out how to get search results from several different columns of two different tables within a database. I have successfully accomplished this, but one column has many many duplicates. I have read on these forums about using the DISTINCT command to eliminate this, but so far have been unsuccessful. Here is the search I'm using...(columns 1, 2, and 3 are from one table and columns 4 and 5 are from another. The results need to be in ascending order (alphabetical) of column1.

select column1, column2, column3, column4, column5 from table1, table2 order by column1;

The result of this is great except that column1 has many duplicates.

I tried entering the search like this...

select distinct column1, column2, column3, column4, column5 from table1, table2 order by column1;

...but that didn't seem to help.

Can someone steer me in the right direction?

Thanks for any and all assistance.

View 8 Replies


ADVERTISEMENT

Search Duplicates

Oct 7, 2007

Hi,


I have 4 tables :


Code Block
SAMPLE
Create Table #Request ( [requestid] int , [customername] Varchar(30) , [stateno] nvarchar(5) , [cityno] int , Callid int, UniqueNo int);

Create Table #Dup ([dupid] int , [cname] Varchar(30), [cityno] int);

Create Table #Call(Callid int,Calltype int,callDetailid int )

Create Table #CallDetail(callDetailId int,empid int)

Insert into #CallDetail VALUES(12123,1)
Insert into #CallDetail VALUES(53423,1)
Insert into #CallDetail VALUES(6532,1)
Insert into #CallDetail VALUES(82323,1)
Insert into #CallDetail VALUES(124235,1)

Insert Into #Call VALUES(111,1,12123)
Insert Into #Call VALUES(112,1,53423)
Insert Into #Call VALUES(114,1,6532)
Insert Into #Call VALUES(123,2,6532)
Insert Into #Call VALUES(134,1,124235)

Insert Into #request Values('324234','Jack','SA023',12,111,0);
Insert Into #request Values('223452','Tom','SA023',12,112,0);
Insert Into #request Values('456456','Bobby','SA024',12,114,0);
Insert Into #request Values('22322362','Guck','SA024',44,123,0);
Insert Into #request Values('22654392','Luck','SA023',12,134,0);

Insert Into #Dup VALUES (1, 'Bobby',12);
Insert Into #Dup VALUES (2, 'Harry',12);
Insert Into #Dup VALUES (3, 'Timothy',14);
Insert Into #Dup VALUES (4, 'Guck',44);
Insert Into #Dup VALUES (5, 'Angela',44);






1. My stored proc will take the CallDetailID.
2. I have to find out the requests made on this calldetail.
3. After getting the request, i want to take the Customername, cityno of the request found and pass it to Dup table to search for any duplicates.
4. If found return those duplicates:


Eg:

I pass in CallDetailID as 6532.
There are 2 records in Call table for this i.e (114,123). From this i get the request rows

456456','Bobby','SA024',12,114,0
'22322362','Guck','SA024',44,123,0


After getting these 2 #Request rows, i have to pass the CustomerName and CityNo of these 2 request rows to the #DUP table to see if there is similar data present. If yes return the rows to the client.


OUTPUT should be

1, 'Bobby',12
4, 'Guck',44


#Dup can contain millions of rows. Please suggest how to do this. Please give t-sql statement.

thanks a ton.

View 3 Replies View Related

Help W/ Stored Procedure? - Full-text Search: Search Query Of Normalized Data

Mar 29, 2008

 Hi -  I'm short of SQL experience and hacking my way through creating a simple search feature for a personal project. I would be very grateful if anyone could help me out with writing a stored procedure. Problem: I have two tables with three columns indexed for full-text search. So far I have been able to successfully execute the following query returning matching row ids:  dbo.Search_Articles        @searchText varchar(150)        AS    SELECT ArticleID     FROM articles    WHERE CONTAINS(Description, @searchText) OR CONTAINS(Title, @searchText)    UNION    SELECT ArticleID     FROM article_pages    WHERE CONTAINS(Text, @searchText);        RETURN This returns the ArticleID for any articles or article_pages records where there is a text match. I ultimately need the stored procedure to return all columns from the articles table for matches and not just the StoryID. Seems like maybe I should try using some kind of JOIN on the result of the UNION above and the articles table? But I have so far been unable to figure out how to do this as I can't seem to declare a name for the result table of the UNION above. Perhaps there is another more eloquent solution? Thanks! Peter 

View 3 Replies View Related

Query For Duplicates

Mar 8, 2007

Hi All.


I have a Table with records and some of them are actually multiple records. I need to query SQL 2005 (compare) field values on more than one column to return these records.

I am an absolute beginner and need some guidance on how to start on this. Can anyone advise.

Thanks.

-Indio

View 4 Replies View Related

T-SQL (SS2K8) :: Rank Duplicates But Only Rows Involved In Duplicates?

Oct 22, 2014

I have a table with 22 million Business records. I can see that there are duplicates when I group by BusinessName and Address and Phone. I'd like to place only the duplicates into a table, with a ranking, oldest business key gets a ranking of 1.

As a bonus I'd like each group to have a distinct group name (although not necessary, just want to know how to do this)

Later after I run more verifications to make sure these are not referenced elsewhere I'll delete everything with a matchRank > 1 out of the main Business table.

DROP TABLE [dbo].[TestBusiness];
GO
CREATE TABLE [dbo].[TestBusiness](
[Business_pk] INT IDENTITY(1,1) NOT NULL,
[BusinessName] VARCHAR (200) NOT NULL,
[Address] VARCHAR(MAX) NOT NULL,

[code]....

View 9 Replies View Related

Query To Find Duplicates

Jul 19, 2004

Hi

I am trying to find when a name has been entered more than once into 1 database table.

I'm currently doing something like this (can't remember exactly, not at work)


SELECT COUNT(*) AS Cnt, Name
FROM tblTable
GROUP BY Name
ORDER BY Cnt Desc

This brings back all the Names in the database and tells me which are duplicates but I want to just have the results of the duplicate values and not the single values.

Hope you can help.

Thanks

View 2 Replies View Related

Query To Show Duplicates

Aug 16, 2005

mytable      fld1 int     primkey                  fld2 varchar(20),                  fld3 varchar(20)From the definition of the above table, how do i do i modify my below query to only select the rows with duplicates in fld2.  I know a groupby with a having count will display the duplicates for a given field, but i want my query to see all the fields and rows that are duplicates.     select fld1, fld2, fld3 from mytable                

View 1 Replies View Related

T-SQL (SS2K8) :: Duplicates In Sub Query

Sep 13, 2014

I have the piece of sql code here below that keeps giving out duplicates. How to resolve this.

isnull((select distinct (SUM(a1.ActualDebit) - SUM(a1.ActualCredit) ) from #MainAccount a1
LEFT OUTER JOIN
#BudgetAccount bb ON aa.AccountID = bb.AccountID AND a1.PeriodStartdate = bb.PeriodStartDate and
a1.DateMonth=bb.DateMonth and a1.Budget = bb.Budget WHERE a1.AccountID = aa.AccountID and
a1.Refdate >= @FROMDATE and a1.Refdate <= @TODATE GROUP BY a1.group1, a1.Group2),0)
As Actual_CurrentMonth,

View 1 Replies View Related

Optimizing A Query To Delete Duplicates

Jul 20, 2005

I have a DELETE statement that deletes duplicate data from a table. Ittakes a long time to execute, so I thought I'd seek advice here. Thestructure of the table is little funny. The following is NOT the table,but the representation of the data in the table:+-----------+| a | b |+-----+-----+| 123 | 234 || 345 | 456 || 123 | 123 |+-----+-----+As you can see, the data is tabular. This is how it is stored in the table:+-----+-----------+------------+| Row | FieldName | FieldValue |+-----+-----------+------------+| 1 | a | 123 || 1 | b | 234 || 2 | a | 345 || 2 | b | 456 || 3 | a | 123 || 3 | b | 234 |+-----+-----------+------------+What I need is to delete all records having the same "Row" when there existsthe same set of records with a different (smaller, to be precise) "Row".Using the example above, what I need to get is:+-----+-----------+------------+| Row | FieldName | FieldValue |+-----+-----------+------------+| 1 | a | 123 || 1 | b | 234 || 2 | a | 345 || 2 | b | 456 |+-----+-----------+------------+A slow way of doing this seem to be:DELETE FROM XWHERE Row IN(SELECT DISTINCT Row FROM X x1WHERE EXISTS(SELECT * FROM X x2WHERE x2.Row < x1.RowAND NOT EXISTS(SELECT * FROM X x3WHERE x3.Row = x2.RowAND x3.FieldName = x2.FieldNameAND x3.FieldValue <> x1.FieldValue)))Can this be done faster, better, and cheaper?

View 3 Replies View Related

Hi, Pls See The Query Inside, Reg. Removing Duplicates..

Mar 17, 2008

-- declared variables
declare @database_name varchar(100), @table_name varchar(100), @primary_key_field varchar(100)
declare @list varchar(8000)
-- set values to variables
set @list = ''
set @database_name = 'data200802_dan'
set @table_name = 'other02'
set @primary_key_field = 'callid'

use database

select @list = @list + column_name + ', '
from information_schema.columns
where table_name = @table_name --table name
and column_name != @primary_key_field --unique identifier
select @list = substring(@list, 1, len(rtrim(@list)) - 1)

--above 5 lines btw came from a helper in the msdn forum. thanks

SELECT DISTINCT @list
INTO '#' + @table_name
FROM @table_name
@table_name + ':'
IF (SELECT COUNT(*) FROM @database_name + '.dbo.' + @table_name) = 0
BEGIN
INSERT INTO
@database_name + '.dbo.' + @table_name + '(' + @list + ')'
SELECT
@list
FROM
'#' + @table_name
END
ELSE
BEGIN
DELETE @database_name + '.dbo.' + @table_name +' ( ' + @list + ')'
GOTO @table_name
END
DROP TABLE '#' + @table_name



the query above is basically.. selecting all the fields from a table in database W/OUT their primary key. then putting them in a temp table.. delete all the records in the original table. then paste the records from the temp table into the original table.

is there a way for this to work? i don't know how to use the variables w/ this script. please help me correcting this query..

this is for removing duplicates btw. thanks!

View 3 Replies View Related

T-SQL (SS2K8) :: Join Query Produce Duplicates?

Aug 12, 2014

join three tables and wont be duplicate records.

I have tried and attached the computed results and also expecting results.

IF OBJECT_ID('tempdb..#tmpExam1')IS NOT NULL DROP TABLE #tmpExam1
IF OBJECT_ID('tempdb..#tmpExam2')IS NOT NULL DROP TABLE #tmpExam2
IF OBJECT_ID('tempdb..#tmpExam3')IS NOT NULL DROP TABLE #tmpExam3

[Code]....

View 4 Replies View Related

SQL Server 2008 :: Remove Duplicates From Query?

Oct 6, 2015

I am working with a bunch of records that have duplicates on the Persid and the intPercentID where there are duplicates I want to remove when I stick them in the temp table, I tried join on tempo table and doing not exists but still inserts, so now I am trying a merge but same thing. how can I keep duplicates from being inserted in the temp table. I made a cursor as well but its slow as heck, but it does work. trying better ways.

Create table #TempStr (STRId int not null Identity(1,1) primary key, Persid int, percentId int, dtCreated datetime, CreatedBy int)

Create table #NewStr (STRId int, Persid int, percentId int, dtCreated datetime, CreatedBy int)

INSERT #TempStr (Persid, percentId, dtCreated, CreatedBy)
select intPersonnelID, intPercentID, dtSubmitted, intSubmittedBy from tblSTR
whereintpercentId in (61,62) group by intPercentID, intPersonnelID, dtSubmitted, intSubmittedBy
UNION ALL

[code]....

View 3 Replies View Related

Help Selecting Duplicates - Added In Full Query

Oct 15, 2007

[SELECT coalesce(a4.key,a3.key,a2.key,a.key) as parent_key,
a.key as child_key,
a.comp_name
FROM .......lots of JOINS..........]

that's what I have, and I need to find all instances where parent_key=child_key.

I've been trying something similiar to...

[SELECT name, count(name), name2, count(name2)
FROM people
GROUP BY name, name2
HAVING (count(name)>1 AND count(name2)>1)]

The problem is that I keep getting error messages because I can't use
the alias' or sum(coalesce(.....)).

I was hoping you guys might have a suggestion for me. Thanks ahead of time.

View 10 Replies View Related

Transact SQL :: Find Duplicates Within Existing Query

Nov 18, 2015

I need to make a selection on join datasets with 2 conditions and populate the results in another dataset(Report).It is working with the fist condition "AccountingTypeCharacteristicCodeId = 3"...

INSERT INTO SurveyInterface.tblLoadISFNotification (OperatingEntityNumber, SDDS, SurveyCodeId, QuestionnaireTypeCodeId, ReferencePeriod, DataReplacementIndicator, PrecontactFlag, SampledUnitPriority)
SELECT ISF.OperatingEntityNumber
       ,[SDDS]
       ,[SurveyCodeId]
       ,[QuestionnaireTypeCodeId]
       ,[ReferencePeriod]
       ,[DataReplacementIndicator]
      
[code]....

Know I also want to add in that new dataset(report) all the duplicates of concatenated variables

ISF.OperatingEntityNumber/ISF.QuestionnaireTypeCodeId 
GROUP BY ISF.OperatingEntityNumber, ISF.QuestionnaireTypeCodeId

View 4 Replies View Related

SQL Server 2008 :: Query Joining Multiple Tables Getting Duplicates?

May 17, 2013

I'm joining several tables and when I add the last one I get duplicate results. How can I get just one for each?

select a.field, b.field, c.field
from atblname as a inner join btblname as b on a.id = b.parent_id
left outer join ctblname as c on a.id = c.parent_id

There are more than one result when joining tbl a and c, but I'm getting a reult for each of them for all results from joining a and b.

View 9 Replies View Related

Converting Duplicates Records Into Non Duplicates?

Jan 26, 2015

Is there a query or a way to convert duplicates value in a column to non duplicates.

View 14 Replies View Related

SQL 2000 MS Search: Boolean Search Doesn't Work When Search By Phrase

Aug 9, 2006

I'm just wonder if this is a bug in MS Search or am I doing something wrong.

I have a query below

declare @search_clause varchar(255)

set @Search_Clause = ' "hepatitis b" and "hepatocellular carcinoma"'

select * from results

where contains(finding,@search_clause)

I don't get the correct result at all.

If I change my search_clause to "hepatitis" and "hepatocellular carcinoma -- without the "b"

then i get the correct result.

It seems MS Search doesn't like the phrase contain one letter or some sort or is it a know bug?

Anyone know?

Thanks

View 3 Replies View Related

SQL Search Query Help!

Sep 11, 2004

I am having hard time figuring out what query I need to write.


What I want to do is, when users enter a word into a textbox, it displays results similar to it. Something similar to what Google does.

This is what I am doing now.
For example I will use "John Smith"

1. I get the first 3 letters of the string "Joh"
2. Get the last letter of the string "h"
3. and this is what I search for "Joh%h%"

This works, but I still have some problems. I want to be able to search like Google, where it recognizes words. Does anybody know how I can do this, or send me a link with an example?

Thank you

View 3 Replies View Related

About Search Query

Aug 2, 2005

I would like to write query which can1.  ignore whether the search keyword is Upper case or lower Case2. deal with tense or verb form related  problem    eg. comparing --> also search compareAny Idea

View 9 Replies View Related

Search Query

Aug 16, 2005

hi all
I have the follwoing search page:
several text boxes in case they want to search for SSN, first name,
last name, zipcode, several dorpdown lists, and a set of radio buttons.
I want to create the SQL statement to display the results
I am checking if any of the text boxes has anything in it, if yes add
"where something = value" to the query, if not then skip... now let's
say it goes through textbox 1 and finds it empty, so it jumps to the
second one it find info in it, how do I deal with the "and" clause
within the query?
any tips are fine.. (im using c#)
thanks

View 7 Replies View Related

Need Query Help For Search

Feb 28, 2006

I am writing a small search feature to return a list of companies whose name "Begins with" a certain string (up to 5 chars) provided by the user via a textbox.  I want the results to only return results that begin with the letter/letters specified.  Below I will put the code that I came up with that isn't working quite how I expected.  I am new to this so any assistance and short explanation would be very much apperciated.
sql="SELECT distinct cm.cmmst_id, cm.cm_compno, cm.cm_cname1 + ' ' + cm.cm_cname2 AS cm_cname1, cm_tele, cm_fax, cm_s16 "
sql=sql & "FROM cmmst cm "
sql=sql & "WHERE cm_cname1 + ' ' + cm_cname2 LIKE '%" companyNameBegins,"'","''") & "%' " sql=sql & "AND (cm_mbtyp='M' OR cm_mbtyp='SUBDIV') " sql=sql & "ORDER BY cm_s16 DESC, cm_cname1 ASC"
companyNameBegins is the string passed in by the user
 
Thanks,
Zoop

View 2 Replies View Related

SQL Search Query

Mar 27, 2001

Hello-

I am having a difficult time figuring out the differences between the following 2 queries. The first query ( which should return around 8000 records) when run against a Sql7 DB with 70000 records will always timeout (timeout set at 30 secs), but the 2nd query will return the correct amount of records ( about 8000 ) in under 8 secs.

Could someone shed some light on this for me?

Thank you
Robert

******Start Query 1******
Select Lot, Title, UserId, HighBidder, HighBid,
BidTimes, DateIn, EndDate, Price, Category, Options,
Reserve, StartDate From Auctions A Where (
((title LIKE '%STUB%' )) or ((description LIKE '%STUB%' ))
) And (Status In ('A', 'P') Or (Status In ('B', 'C')
And EndDate >= '02/25/2001')
) And ((A.Reserve > A.Price And A.Reserve Between 0
And 999999.99)
Or (A.Price Between 0 And 999999.99))
Order By Lot Asc




*****End Query 1*****

******Start Query 2******
Select Lot, Title, UserId, HighBidder, HighBid,
BidTimes, DateIn, EndDate, Price, Category, Options,
Reserve, StartDate From
(Select Lot, Title, UserId, HighBidder, HighBid,
BidTimes, DateIn, EndDate, Price, Category, Options,
Reserve, StartDate,Description From Auctions A Where
(A.Status In ('A', 'P')

Or (A.Status In ('B', 'C') And
A.EndDate >= '02/25/2001')

) And ((A.Reserve > A.Price And A.Reserve Between 0
And 999999.99)
Or (A.Price Between 0 And 999999.99))
) as X
Where (

( (title LIKE '%STUB%' ))


or ( (description LIKE '%STUB%' ))



)
Order By Lot Asc

******End Query 2******

View 1 Replies View Related

Need Help On Search Query.

Sep 6, 2007



Hello All!

I'm building a serch page on website. It basicly searches 2 tables. Right now, the way I have it so the user can enter a value in one of the fields, and not the others. And it will return the value. For example, put in '34' in the age field, it will return all people with 34. Now if you add 'Jones' to the last name field, it will return all people with the age of 34, and all people with Jones.

But what if I want it so it returns only the people with the age 34, and Jones. But leave the other fields blank. The later than add 'Jim' to the first name, so now it will return 34, jones, jim. I tried to use the AND operator, but then it requires that field to be filled. Here is my code.

ALTER PROCEDURE [dbo].[SrchActiveII]

@FName nvarchar(50)=NULL,
@LName nvarchar(50)=NULL,
@DOB nvarchar(50)=NULL,
@Acct nvarchar(50)=NULL,
@Login nvarchar(50)=NULL,
@Status nvarchar(50)=NULL,
@Rmark nvarchar(255)=NULL,
@Room nvarchar(50)=NULL,
@Age nvarchar(50)=NULL,
@Type nvarchar(50)=NULL,
@Misc bit = NULL
AS
BEGIN
SELECT Active_Orders.First_Name, Active_Orders.Last_Name, Active_Orders.Account_Number, Order_Status.Status, Active_Orders.Remarks,
Locations.Loct_Desc, Active_Orders.Rm_Desc, Active_Orders.Age, Active_Orders.Type, Active_Orders.Stat,
Active_Orders.Order_ID, Active_Orders.Login, Active_Orders.Misc
FROM Active_Orders INNER JOIN
Order_Status ON Active_Orders.Status_ID = Order_Status.Status_ID INNER JOIN
Locations ON Active_Orders.Location_ID = Locations.Location_ID
WHERE (Active_Orders.First_Name =@FName) OR (First_Name IS NULL) OR
(Active_Orders.Last_Name =@Lname) OR (Last_Name IS NULL) OR
(Active_Orders.DOB = @DOB) OR (DOB IS NULL) OR
(Active_Orders.Account_Number = @Acct) OR (Account_Number IS NULL) OR
(Order_Status.Status = @Status) OR (Status IS NULL) OR
(Active_Orders.Remarks = @Rmark) OR (Remarks IS NULL) OR
(Active_Orders.Rm_Desc = @Room) OR (Rm_Desc IS NULL) OR
(Active_Orders.Age = @Age) OR (Age IS NULL) OR
(Active_Orders.Type = @Type) OR ([Type] IS NULL) OR
(Active_Orders.Login = @Login) OR ([Login] IS NULL) OR
(Active_Orders.Misc = @Misc) OR (Misc IS NULL)
END

Thanks!

Rudy

View 7 Replies View Related

Need Help In Search Query...

Apr 9, 2007

Hi,

I am making a search query for jobs site. I have a situation that if user type following string in my search box:



Web Developer Designer ISF New York



It should display all jobs conatins any one of given words. following are columns in my jobs table: (Query will be applied on Location, Title, Description, Company fields)








Jobs


JobID




PostedDate


Company


URL


Location


Title


Description


Contact







please help me what will be the query.

Thanks,

Bye.

View 7 Replies View Related

Simple Search Query

Mar 16, 2007

Hi,

 

I'm writing
small search engine for my page. I need SQL query that could do this:

 

Source:

 

tblColours

------------

Red

GreenRed

White Red

Yellow

Blue Green

Yellow RedF

 

Search
string: Red

 

Required
results:

 

Red

White Red

Yellow RedF

 

As you can
see I need all occurrences of word Red and word Red* but I don’t need *Red or
*Red* so I can't use LIKE %Red% :(.

 

P.S. Sorry
for my English.

Fizikas.

View 1 Replies View Related

Sql Query To Search In Two Tables

Jun 25, 2007

 i have two tables,
Opportunity

[OpporID] [numeric](18, 0) IDENTITY (1000, 1) NOT NULL ,
[OpportunityID] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[OpportunityTypeID] [numeric](10, 0) NOT NULL ,
[SLABased] [int] NOT NULL ,
[LoginID] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DateCreated] [datetime] NOT NULL ,
[AccountID] [int] NOT NULL ,
[GeographyID] [int] NOT NULL ,
[VerticalID] [int] NOT NULL ,
[BDMID] [int] NOT NULL ,
[Probability] [int] NOT NULL ,
[PASStatus] [int] NULL ,
[InsertedDate] [datetime] NULL ,
[InsertedBy] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UpdatedDate] [datetime] NULL ,
[UpdatedBy] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UpdatedFlag] [int] NULL

and SKILL
[SkillNo] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[OpportunityID] [numeric](18, 0) NOT NULL ,
[OrderId] [numeric](18, 0) NOT NULL ,
[PracticeID] [int] NULL ,
[SkillID] [int] NOT NULL ,
[NoOfPeople] [int] NOT NULL ,
[Clientinterview] [int] NOT NULL ,
[Location] [int] NOT NULL ,
[JDAttached] [int] NOT NULL ,
[JDFilePath] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Status] [int] NULL ,
[Experience] [int] NULL ,
[InsertedDate] [datetime] NULL ,
[InsertedBy] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[UpdatedDate] [datetime] NULL ,
[UpdatedBy] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UpdatedFlag] [int] NULL ,
[GeoLocation] [int] NULL
)

i want to make a stored procedure for custom search on these two tables
with the following fields given to the user as an option to make his
choice..
from opportunity table -
OpportunityTypeID,SLABased,AccountID ,
GeographyID,
VerticalID,
BDMID,
Probability

and from skill table
SkillID, Location, GeoLocation

and return all the fields of opportunity table.

Can some make the stored procedure for me..

thanks a lot.

View 3 Replies View Related

Table And Search Query - Help Me Please.

Oct 16, 2007

Hi!In short description I want to make table with Articles and search Query for this table like there is search engine at templatemonster.com with templates and categories. Basicly I have table:- id - cat_name- 1 - Software- 2 - Hardware- 3 - Games- 4 - Internet- 5 - Events etc.And for example Article is in Software, Games and Events category, "1,3,5".Now user select to show articles in Games and Events categories, so "3,4,5"How write this search query? I don't have idea. 

View 2 Replies View Related

Best Query/Search Method

Nov 5, 2007

Hi,
I'm wondering about the following:
I have come across an InfoPath Forms application who's code is scripted in javascript and who's data seems to be in XML files.An analyst at that company told me they suspect the data is ALSO in SQL Server... somewhere.  They can't seem to find it though.  I havereviewed the .js code and some methods are called for which I can find no source.  I believe those methods execute OK because they're foundinside some DLL.
I'm thinking I would enter a new record using the form in InfoPath using some datavalue that I can expect will be unique.. like a lastname who's first three chars is ZZZ or something like that.  Subsequently, I'd search each column in each table in each DB on the server to see if I can locate it somewhere.
So, my question is what is the best approach for this?  I have access to the db, table and column names.  I know I can write a small vb.net piece of code to execute my search.  But, is there some better way using some sql procedure (or using the full text catalog) instead or any other tool(s)?  
Thanks in advance for your advise.
Stewart
 
 

View 3 Replies View Related

Help Needed In SQL Query Search!

Aug 2, 2005

Hi everyone,
I'm trying to implement SQL Server database search. The details are:-1. I have table called EMPLOYEE has FNAME,LNAME etc cols.2. User might look for any employee using either FNAME or LNAME3. I have search box in asp.net where user could enter search stringThe sample data:FNAME       LNAMEabc         georgedef         georgerkis        litarose        lita
The query i wrote:SELECT * FROM EMPLOYEE WHERE lname like '%' + searchArg + '%'My problem is:-1. let's say user is looking for employee "george"; In search string instead of typing actual word "george", user could type "jeorge"; because the name pronounce or sounds like similar.Same thing with user could type "leta" instead of "lita". Again these are all similar sounds.
When you look for "jeorge" in GOOGLE; it says "did you mean george"; i would like implement something like that. somewhere i saw SOUNDEX would do what i am looking for; but i no luck for me.
Is this possible anyway in T-SQL or Fulltext search.
Your help is greatly appreciated.
ThanksBob

View 1 Replies View Related

Upgrading A Search Query

Dec 6, 2005

 
i have an sp that does not use full text searching in SQL 2000 i was wondering if someone could point me in the right direction of changing/upgrading this to a full text search? would you recommend upgrading the search sp? the following is the sp i have(it uses a function to count the number of words):CREATE PROCEDURE SearchCatalog (@PageNumber tinyint,@ProductsOnPage tinyint,@HowManyResults smallint OUTPUT,@AllWords bit,@Word1 varchar(15) = NULL,@Word2 varchar(15) = NULL,@Word3 varchar(15) = NULL,@Word4 varchar(15) = NULL,@Word5 varchar(15) = NULL)AS
/* Create the temporary table that will contain the search results */CREATE TABLE #SearchedProducts(RowNumber SMALLINT NOT NULL IDENTITY(1,1), ProductID INT, Name VARCHAR(100), Description VARCHAR(1000), Price MONEY, ImagePath VARCHAR(100), Rank INT, ImageALT VARCHAR(100), ArtistName VARCHAR(50), Stock INT, SearchCoverQuality INT, SearchAlbumQuality INT)
/* Populate #SearchedProducts for an any-words search */IF @AllWords = 0    INSERT INTO #SearchedProducts           (ProductID, Name, Description, Price, ImagePath, ImageALT, ArtistName, Stock, SearchCoverQuality, SearchAlbumQuality, Rank)   SELECT Product.ProductID, Product.Name, Product.Description, Product.Price, Product.ImagePath,            Product.ImageALT, Artist.ArtistName, Product.Stock, AlbumSingleDetails.CoverQualityID, AlbumSingleDetails.QualityID,          3*dbo.WordCount(@Word1, Name)+dbo.WordCount(@Word1, Description)+dbo.WordCount(@Word1, ArtistName)+          3*dbo.WordCount(@Word2, Name)+dbo.WordCount(@Word2, Description)+dbo.WordCount(@Word2, ArtistName)+          3*dbo.WordCount(@Word3, Name)+dbo.WordCount(@Word3, Description)+dbo.WordCount(@Word3, ArtistName)+          3*dbo.WordCount(@Word4, Name)+dbo.WordCount(@Word4, Description)+dbo.WordCount(@Word4, ArtistName)+          3*dbo.WordCount(@Word5, Name)+dbo.WordCount(@Word5, Description)+dbo.WordCount(@Word5, ArtistName)          AS TotalRank   FROM Product INNER JOIN (Artist INNER JOIN AlbumSingleDetails ON Artist.ArtistID = AlbumSingleDetails.ArtistID) ON Product.ProductID = AlbumSingleDetails.ProductID   ORDER BY TotalRank DESC  
/* Populate #SearchedProducts for an all-words search */IF @AllWords = 1    INSERT INTO #SearchedProducts           (ProductID, Name, Description, Price, ImagePath, ImageALT, ArtistName, Stock, SearchCoverQuality, SearchAlbumQuality, Rank)   SELECT Product.ProductID, Product.Name, Product.Description, Product.Price, Product.ImagePath,   Product.ImageALT, Artist.ArtistName, Product.Stock, AlbumSingleDetails.CoverQualityID, AlbumSingleDetails.QualityID,          (3*dbo.WordCount(@Word1, Name)+dbo.WordCount(@Word1, Description)+dbo.WordCount(@Word1, ArtistName)) *          CASE              WHEN @Word2 IS NULL THEN 1              ELSE 3*dbo.WordCount(@Word2, Name)+dbo.WordCount(@Word2, Description)+dbo.WordCount(@Word2, ArtistName)          END *          CASE              WHEN @Word3 IS NULL THEN 1              ELSE 3*dbo.WordCount(@Word3, Name)+dbo.WordCount(@Word3, Description)+dbo.WordCount(@Word3, ArtistName)          END *          CASE              WHEN @Word4 IS NULL THEN 1              ELSE 3*dbo.WordCount(@Word4, Name)+dbo.WordCount(@Word4, Description)+dbo.WordCount(@Word4, ArtistName)          END *          CASE              WHEN @Word5 IS NULL THEN 1              ELSE 3*dbo.WordCount(@Word5, Name)+dbo.WordCount(@Word5, Description)+dbo.WordCount(@Word5, ArtistName)          END          AS TotalRank   FROM Product INNER JOIN (Artist INNER JOIN AlbumSingleDetails ON Artist.ArtistID = AlbumSingleDetails.ArtistID) ON Product.ProductID = AlbumSingleDetails.ProductID   ORDER BY TotalRank DESC
/* Save the number of searched products in an output variable */SELECT @HowManyResults=COUNT(*) FROM #SearchedProducts WHERE Rank>0
/* Send back the requested products */SELECT ProductID, Name, Description, Price, ImagePath, ImageALT, ArtistName, Stock, Rank,        CASE SearchCoverQuality            WHEN 1 THEN '5stars.gif'            WHEN 2 THEN '4stars.gif'            WHEN 3 THEN '3stars.gif'            WHEN 4 THEN '2stars.gif'            ELSE '1stars.gif'        END AS CoverQuality,        CASE SearchAlbumQuality            WHEN 1 THEN '5stars.gif'            WHEN 2 THEN '4stars.gif'            WHEN 3 THEN '3stars.gif'            WHEN 4 THEN '2stars.gif'            ELSE '1stars.gif'        END AS AlbumQualityFROM #SearchedProductsWHERE Rank > 0  AND RowNumber BETWEEN (@PageNumber-1) * @ProductsOnPage + 1                     AND @PageNumber * @ProductsOnPageORDER BY Rank DESCi was wondering if i can use full text search on many tables at once?thanks in advance!Tuppers

View 1 Replies View Related

Sql Query For Search Engines

Dec 8, 1999

Hi friends,
Can u let me know how the sql-query, that displays only the first 20 records,and while clicking next button the subsequent records should be diplayed in a web appliaction(search). i want to implement through ADO objects.Because if the search query results in hundreds of records and everything is taken into the memory it will badly affect the performance of the system !right?
Regards
rajesh

View 1 Replies View Related

Keywords Search Query

Feb 6, 2007

Hi,
I'm trying to do a query to do a search by keywords. For example if the user searches for "Hottest restaurants in Atlanta" i want to write a query that searches that exact phrase first in a database field called keywords and if it shows up there then that record should be displayed first.. and then after do a search for EACH one of the words in a number of fields in the database and see if it shows up.

here is what i have so far but it always returns the same thing. any ideas?

$searchArray=explode(" ",$search);
$query = "SELECT ID,name,description from restaurants WHERE keywords = '$search' OR (";

foreach($searchArray as $key){
$query = $query . " (name LIKE '%$key%' OR subType LIKE '%$key%' OR features LIKE '%$key%' OR neighborhood LIKE '%$key%' OR region LIKE '%$key%') OR";

}

$query = substr($query,0,strlen($query)-3);
$query = $query . ") order by keywords";



any ideas?

View 11 Replies View Related

Need Help Writing Search Query

Feb 17, 2005

I am not very familiar with the syntax of MS SQL and I am trying to write a stored procedure which would do a search and return matching records.
This is what I need to achieve:
for instance I create a form with 4 text fields
- First Name
- Last Name
- Employee ID
- Date

I am interested in writing a stored procedure that would run a select query based on the input in the text fields
e.g.
- if the user enters First Name and the Last Name (leaving Employee ID and Date fields blank) query should be something like
select * from Employee where FirstName like @FirstName and LastName like @LastName
- or if the user enters only the Employee ID stored procedure should run a query similar to
select * from Employee where EmployeeID like @EmployeeID

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved