I've done some simple sql's for searching a field using Like,
But this one is different.
I am adding a param named @searchText
I would like to bring back all records in all the fields listed
below that has that string in the field...
WHERE a.manufacturer = b.manufacturerID
AND a.location = c.locationID
AND a.Status = d.statusID
AND a.EquipmentType = e.ID
AND a.calLab = f.ID
AND a.testTechnology = g.id
AND (c.locationID = @location OR @location = 0)
So, each line/field above I want to search for the string and include
in the dataset.
Hi, i'm trying to do a full text search on my site to add a weighting score to my results. I have the following database structure: Documents: - DocumentID (int, PK) - Title (varchar) - Content (text) - CategoryID (int, FK) Categories: - CategoryID (int, PK) - CategoryName (varchar) I need to create a full text index which searches the Title, Content and CategoryName fields. I figured since i needed to search the CategoryName field i would create an indexed view. I tried to execute the following query: CREATE VIEW vw_DocumentsWITH SCHEMABINDING ASSELECT dbo.Documents.DocumentID, dbo.Documents.Title, dbo.Documents.[Content], dbo.Documents.CategoryID, dbo.Categories.CategoryNameFROM dbo.Categories INNER JOIN dbo.Documents ON dbo.Categories.CategoryID = dbo.Documents.CategoryID GOCREATE UNIQUE CLUSTERED INDEX vw_DocumentsIndexON vw_Documents(DocumentID) But this gave me the error: Cannot create index on view 'dbname.dbo.vw_Documents'. It contains text, ntext, image or xml columns. I tried converting the Content to a varchar(max) within my view but it still didn't like. Appreciate if someone can tell me how this can be done as surely what i'm trying to do is not ground breaking.
I would like to search a table for a phrase, or for a partial phrase,eg on table product - for name or description, or name + descprition.How does one say select * from product where name + description like%phrase%or contains phraseCurrently I can get where name, or where descriotion like %phrase%,eg, where name like krups, or where description like coffee makerBut if I search for where name like %krups coffee maker% i get noresults. krups is in the name field, coffee maker is in thedescription field.Thanks,-M
I am using MS SQL 2012. I have a table that contains all the data that I need, but I need to summarize the data and also add up decimal fields while at it. Then I need a total of those added decimal fields. My data is like this:
I have Providers, a unique ID that Providers will have multiples of, and then decimal fields. Here are my fields:
Clients Contacts (multiple contacts for one client)
I'm trying to do a search that pulls a contacts where the search matches either the First Name, last Name or the clients name. If it does return clients, I'd like it to also return all the contacts associated with it.
I have two problems:
1. The query is not bringing up a lot of clients. In many cases a letter brings nothing back. Like G and H even though A and B return results.
2. If it finds a client it only returns one contact. I'd like it to return all contacts for the client.
Here's my query:
SELECT addressbook.clientid, clients.clientname, addressbook.addressid, addressbook.fname, addressbook.lname FROM clients, addressbook where clients.clientid = addressbook.addressid AND (addressbook.fname LIKE '".strtoupper($_GET['txtsearch'])."%' OR addressbook.lname LIKE '".strtoupper($_GET['txtsearch'])."%' OR clients.clientname LIKE '" . strtoupper($_GET['txtsearch'])."%')
Hi,I want to run queries on a table that has binary fields in it. How do Ifilter on a binary field? E.g. One of the fields is called'Account_Manager_ID' which is binary - I would like to do a simple Select *from company where Account_Manager_ID = 'blah blah blah'When I do this, it returns no data. How do I get round this?Thanks!
Does anyone know how to write a stored procedure that will search through every field of every table of every table of every database to look for a specific data that matches a certain pattern and return the database, table, and field name?
I want to search in fulltextindexes for multiple searchterms in multiple columns. The difficulty is: I don't want only the records with columns that contains both searchterms. I also want the records of which one column contains one of the searchterm ans another column contains one of the searchterms.
For example I search for NETWORK and PERFORMANCE in two columns. Jobdescr_________________________|Jobtext Bad NETWORK PERFORMANCE________|Slow NETWORK browsing in Windows XP Bad application PERFORMANCE_______|Because of slow NETWORK browsing, the application runs slow.
I only get the first record because JobDescr contains both searchterms I don't get the second record because none of the columns contains both searchterms
I managed to find a workaround:
SELECT T3.jobid, T3.jobdescr FROM (SELECT jobid FROM dba.job WHERE contains(jobdescr, 'network*') or CONTAINS(jobtext, 'network*') ) T1 INNER JOIN (SELECT jobid FROM dba.job WHERE contains(jobdescr, 'performance*') or CONTAINS(jobtext, 'performance*')) T2 ON T2.Jobid = T1.Jobid INNER JOIN (SELECT jobid, jobdescr FROM dba.job) T3 ON T3.Jobid = T1.Jobid OR T3.Jobid = T2.JobId It works but i guess this will result in a heavy database load when the number of searchterms and columns will increase.
I apologize for the newbie sort of question, but I could not find an answer in an SQL book nor via Google.
I wish to search for a text string in ALL fields of a table. This will be used to provide a simple search box in a web application.
So far, the only method I've found to accomplish this is follows:
SELECT * FROM Inventory WHERE SerialNumber LIKE '%searchstring%' OR UserName LIKE '%searchstring%' OR Location LIKE '%searchstring%' ... etc
My goal is to accomplish something like the following. This, of course, does not execute properly since * can only be used following SELECT, but you can get an idea of the target behavior:
SELECT * FROM Inventory WHERE * LIKE '%searchstring%'
I'm using MSDE with Visual Basic .Net. Any suggestions on how to accomplish this?
I am trying to create a query that will show me who is phoning who in anorganisation from available Telephone Billing information. I am creating aMSAccess 2000 database with a few few tables, two of which are:TableMembers: (containg fields Refs, DateCreated, MembershipNo,OfficeLocation ...NB: Refs has a Primary Key - No Duplicates)TablePeople: (containing fields: Refs, Name, Addr, TelHome, TelWork,TelMobile & TelFax)TableTelBills: (containing fields: Refs, TelNo, DateCalled, Duration,TelType)I am trying to create a query that will use a simple searching criteria eg.,Like "*" [Enter the Tel No or part Tel No to search:] & "*"to search all the Tel fields in the TablePeople and TableTelBills (TelHome,TelWork, TelMobile, TelFax and TelNo) but am running in difficulties.I start by creating a query and adding the tables TablePeople andTableTelBills and TableMembers.I use the Refs from the Table Members as a base criteria but do not know howto create criteria that will search all Tel fields at once!I would appreciate any and all help people!Jan
Greetings,I used to do this kind of query in Ingres, where I concatenate(+)various fields as one field and search accordingly.Is there an equivalent method in SQL server ?SELECT a.rsrcid,a.rsrchqnumber,c.perslastname,c.persfirst name,b.asgtid,b.asgtactualstartdate,b.asgtactualenddate ,CASE b.enumstateWHEN '2' THEN 'Running'WHEN '3' THEN 'Cancelled'WHEN '4' THEN 'Closed'WHEN '6' THEN 'Open'END AS statusFROM pblocal.dbo.resources aINNER JOIN pblocal.dbo.assignments b ON b.asgtrsrcguid = a.rsrcguidINNER JOIN pblocal.dbo.persons c ON c.persguid = a.rsrcpersguidWHERE a.rsrcid+a.rsrchqnumber+c.perslastname+c.persfirst name not in(SELECT e.rsrcid+e.rsrchqnumber+g.perslastname+g.persfirst nameFROM dtlocal.dbo.resources eINNER JOIN dtlocal.dbo.assignments f ON f.asgtrsrcguid = e.rsrcguidINNER JOIN dtlocal.dbo.users h ON h.userguid = e.rsrcuserguidINNER JOIN dtlocal.dbo.persons g ON g.persguid = h.userpersguid)
I am trying to write a simple search page that will search all the fields in a database to find all records that match a user input string. The string could happen anywhere in any of the fields. I have a dataset and can write a query but am unsure what the format is for this simple task. I figured it would look like this:
SELECT Table.* FROM Table WHERE * = @USERINPUT But thats not working. Can someone help.? Thanks..
Hi All,I'm familiar with the syntax that looks like this:SELECT * FROM Users WHERE Email LIKE '%aol%'Which would return all users that whose Email column contains "aol".However, if I wanted to do that sort of a search using a prepared statement, how would I do it?I can't simply doSELECT * FROM Users WHERE Email LIKE '%@MatchAgainst%'Can somebody clue me in?
I required to build the search feature for my application which contains combination of at least 20 search fields e.g firstname, lastname. date of birth, sign up date ,etc... I am just wondering what is the best way to do it ,should I create stored procedure with 20 input parameters or should I build it based on each search fields. I need to provide the search results via web services. Could anyone help me? Thank you
Hi, I am trying to build search engin with 11 parameters in 4 different tables in the database. For example: In search.aspx I have 11 textboxes namely nameTextbox, phoneTextbox, nationalityTextbox, ageTextbox etc. And in the result.aspx page I have gridview which post data from the database if the search match. I wrote this stored procedure. P.S please ignore the syntax. @name var(30),
@nationality (30),
@phone int,
etc
as
Select a.UserId, b.UserId, c.UserId FROM Table1 a, Table2 b, Table3 c
WHERE
name LIKE '%' @name '%'
OR nationality LIKE '%' @nationality '%'
OR phone LIKE '%' @phone '%'
etc
But I got an error when I am trying to execute this code because the nulls values so I wrote 1 @name var(30), 2 3 @nationality (30), 4 5 @phone int, 6 7 etc 8 9 as 10 11 12 13 Select a.UserId, b.UserId, c.UserId FROM Table1 a, Table2 b, Table3 c 14 15 WHERE 16 17 name LIKE '%' ISNULL(@name, '') '%' 18 19 OR nationality LIKE '%' ISNULL(@nationality,'') '%' 20 21 OR phone LIKE '%' ISNULL(@phone,'') '%' 22 23 etc 24 25
Also the error still exist. What is the best way to search for multiple parameters in multiple tables ?
I'm heaving a heck of a time to get a sql query working. I'm being passed a series of zip codes and I need to return a list of all the rows that match those zip codes in a database of about 40,000 leads.
Can someone clue me in to what the commandtext of my query should look like?
Please point me in the direction of a tutorial that will do help me do the following:
The database has been previously created and the users first & last names are in the respective columns. There is also a column that should contain the full name of the user but as such it only contains <NULL>. Is there a way to pull the first name and last name from the db then write both names to column3?
I have a table that tracks some dates, and I am looking for an SQL statemet that will check all of the fields to see if a prespecified data range is true. For exampe you enter a start and end date and then then the SQL statement would check about 12 different fields to see if any of the dates are within that range. I am trying to use an or statement, but to no avail. So if you have any help it would be greatly appreciated.
I have two issues I'm trying to deal with in my code.
1. I'm trying to group by first and last name, which are in two different columns 2. I'm trying to take an average of pay per miles.
Neither of these is working well. Actually, neither is working at all.
This is the code!
SELECT OD.DriverID, (W.FirstName + W.LastName AS 'Driver'), DATEADD(dd,(DATEDIFF(dd,0,O.ReadyTimeFrom)),0) AS Date, DATENAME(dw,O.ReadyTimeFrom) AS DayOfTheWeek, Count(OD.OrderID) AS 'Total Orders', SUM(O.Distance) AS OrderMiles,
Hi all,I'm running into a road block, and I know I've done this before. I'mgetting fields from two tables, and I need to do a count of similaritems with it showing some extra info.Here's my fields:Log.LogId - IntLog.LogDispatcherID - IntOfficer.OfficerID - IntOfficer.OfficerFirstName - VarcharOfficer.OfficerLastName - VarcharI can get the info I need without a count with this:select a.LogID,a.LogDispatcherID,b.OfficerFirstname + ' ' + b.OfficerLastname as OfficerNamefrom[Log] a, Officer bwhere a.LogAssigned1 = b.OfficerIDBut when I try to add a count and group-by it errors out:select Count(a.LogID) as LogCount,a.LogDispatcherID,b.OfficerFirstname + ' ' + b.OfficerLastname as OfficerNamefrom[Log] a, Officer bwhere a.LogAssigned1 = b.OfficerIDGroup By a.LogIDI've done this before, but this isn't working. It's giving the error"it is not contained in either an aggregate function or the GROUP BYclause" for each field other then LogID.How can I do this? I want output similar to this:LogCountLogDispatchIDOfficerName334Tom Jones422John Smith.... EtcThanks for any suggestions or ideas...Sam Alex
How can I update each record in a table, based on a value in another tablewith a single SQL statement?For example, suppose I have the following two tables:Table1Name Something Color-----------------------------------------John GHAS BlueJohn DDSS BlueJohn EESS BluePaul xxxx RedRingo HJKS RedRingo FFFS RedSara hjkd PurpleSara TTHE PurpleJimi sdkjls GreenTable2Name Color------------------------John ?Paul ?Ringo ?Sara ?Jimi ?How can I update the color field in table 2 to correspond with the colorfield in table1 (so I can normalize the db and delete the color field fromtable1)?I know I could open table2 and loop through within my app; just wonderingabout a single SQL statement that would do it. I need a similar technique inother places as part of my app.Thanks,Calan
I have a search page, containing 3 drop down lists. Theses are used to match data in 3 seperate collumns of a table.I can get the search to work when all boxes are completed, but I need to be able to leave some blank.A similar question was asked in http://forums.asp.net/626941/ShowPost.aspxAnd a very good solution was give: http://www.sqlteam.com/item.asp?ItemID=2077But I don't seem to be able to get any of these examples working!If I use the SET @SQL method, I get an error message saying that my input parameter has not been declared, when it has.When I use the COALESCE method, there is no data returned.(I am passing the data drop down list data fin the search page through the query string to a results page).Does anyone know of any other examples, or sample coding??Thanks...
I have a table with 9 fields that I need to check. I want to check if "text1" and "text2" is in any of thoose 9 fields. But I dont want to check the combination of "text1" and "text2" in the same column. I want for example be able to check if "text1" is in column 1 to 9 and if "text2" is in column 1 to 9. Can someone give me a suggestion on how to do this?
I want to search through 3 tables (TableB, TableC, TableD) to find which hdr_ctl_nbr(s) are on those tables but not on TableA. In other words, TableA should match the combined tables B, C, and D but it doesn't, so I want to find what's missing on TableA. I know how to search TableB (see SELECT below) but how would I add TableC and TableD to this statement ? Thanks, Jeff
Select hdr_ctl_nbr, count(*) from TableA c where c.hdr_ctl_nbr not exists (select hdr_ctl_nbr from TableB) group by c.hdr_ctl_nbr
I have a am doing some date calcs () . The situation is that I have a Move date (date a customer moved to a new home). I want to calculate their sales for the following 0-3 months after the move (month 0 being the move month). I have the month and year of the move (MthStart, YrStart), and I am adding 3 to MthStart to get the MthEnd of that 0-3 month period. I will then find sales BETWEEN YrStart&MthStart AND YrEnd&MthEnd (there is a YrMth field in the sales table)
Of course, for MthStarts 10, 11, and 12, the ends are 13, 14, and 15. So for these, I need to subtract 12, and increment the YrEnd by 1.
I am wondering if there is a way to update both the MthEnd and YrEnd fields at one time instead of separate SETs (or maybe I am just thinking about this the hard way to begin with). Is there a way to update both in a single CASE statement like WHEN MthEnd> 12 THEN MthEnd-12 AND YrEnd+1?
I'm having trouble using Aggregate functions with multiple fields. Here is what I'm trying to do:
SELECT REPLACE(Cust_Key, sum(PRODUCT_CHARGE), count(Tracking_Number), Tracking_Number FROM Shipments = '2008-05-05' Group By Cust_Key, Tracking_Number
I can get this to work when I use Group By for a single field like Cust_Key but I don't know how to make it work for multiple fields. What is the best way to write something that contains a few aggregate functions on top of multiple fields?
