Users have to answer 17 simple yes/no questions and the answers are stored in an column for each question as tinyint 0/1 values.
At least that's what seems reasonable to me at the moment.
The table is under my control so I could change it if needed.
Now from several tenthousend or maybe hundreds of thousends of entries I need to find those with the closest match. Of course, I need all of the entries that have the exact same answers and this is no problem. But - at least if there are not enough full matches - then I need all records that have maybe 16,15,14... matches out of the 17 answers.
I have not yet the idea on how to handle this without quering 17*16 different answer schemes.
Hi folks I have a section in my networking site where people can write things about themselves, such as likes dislikes, hobbies etc for their profile. I am looking to provide a very basic profile matching service, and have done a bit of searching to find out what the best way to approach this feature is. I have looked at using the sql LIKE clause, but since the profile information can be any length, there seem to be too many variables to account for. I have also looked at identifying keywords in profile blurbs to do the matching too, but I am struggling to see how this could be done with relative ease. My reason for posting this is simply to get some experienced knowledge about where to start the problem, of even some resources to look at that I may not have found yet. If anyone could give me some advice on where to start i would be very grateful I am develping in vb, with an sql server 2005 back-end
Given an ID (column B), I need to find which IDs have identical data.That is, given '200', I want the desired result to be:100The idea is that the system sees that id=200 has 5 records with theindicated data in cols C and D.It should then find any other ids with the exact same data for thosecolumns.Note, in this case, both 200 and 100 have (30:1, 30:2, 30:3, 40:4,40:5) so they match. 300 and 400 should NOT be returned.Any bright ideas out there? Thanks!DECLARE @a TABLE(A int, B int, C int, D int)DECLARE @b TABLE(A int, B int, C int, D int)INSERT INTO @a (A, B, C, D) VALUES (1, 100, 30, 1)INSERT INTO @a (A, B, C, D) VALUES (2, 100, 30, 2)INSERT INTO @a (A, B, C, D) VALUES (3, 100, 30, 3)INSERT INTO @a (A, B, C, D) VALUES (4, 100, 40, 4)INSERT INTO @a (A, B, C, D) VALUES (5, 100, 40, 5)INSERT INTO @a (A, B, C, D) VALUES (6, 200, 30, 1)INSERT INTO @a (A, B, C, D) VALUES (7, 200, 30, 2)INSERT INTO @a (A, B, C, D) VALUES (8, 200, 30, 3)INSERT INTO @a (A, B, C, D) VALUES (9, 200, 40, 4)INSERT INTO @a (A, B, C, D) VALUES (10, 200, 40, 5)INSERT INTO @a (A, B, C, D) VALUES (11, 300, 30, 1)INSERT INTO @a (A, B, C, D) VALUES (12, 300, 30, 2)INSERT INTO @a (A, B, C, D) VALUES (13, 300, 40, 3)INSERT INTO @a (A, B, C, D) VALUES (14, 400, 40, 4)INSERT INTO @a (A, B, C, D) VALUES (15, 400, 40, 5)SELECT * FROM @a
I have two tables in my database. Some matching rules are associated with these tables and I want a query which will retrieve those data based on the matching rules provided below:
I have a table called MessageBoard. It has a column called Messages. A user can type text including any html tags through a text area ans when he saves it by clicking a button, the content typed by the user is saved in the MessageBoard Table (in the Messages) column. So once saved, the html tags are kept intact. If I have to find and replace certain html tags, what kind of SQL Query I have to write? For example I want to find all the <pre> </pre> tags and replace it with <p> </p> tags. How do I do this?
We have an application where we want to check to see if the vehicle part on a job matches to our internal parts inventory (PartsInventory table) before we order it. The problem is that sometimes the part number matches exactly and sometimes the part number has '-' or space but if those are removed, will match to our internal part number. Below is what I have so far but it only matches exact part numbers. One example would be if our part number was 1013738-00-C but the job (in RepairOrderLines) had a part number of 101373800C we should consider it a match. Both PartNumbers are varchar(30).
SELECT dbo.PartsInventory.PartNumber, dbo.PartsInventory.PartDescription, dbo.PartsInventory.VehicleMake FROM dbo.PartsInventory INNER JOIN dbo.RepairOrderLines ON dbo.PartsInventory.PartNumber = dbo.RepairOrderLines.PartNumber INNER JOIN dbo.RepairOrder ON dbo.RepairOrderLines.RecordID = dbo.RepairOrder.RecordID INNER JOIN dbo.Vehicles ON dbo.RepairOrder.VehicleID = dbo.Vehicles.VehicleID AND dbo.PartsInventory.VehicleMake = dbo.Vehicles.VehicleMake WHERE (dbo.RepairOrderLines.RecordID = 46001)
Let's say I have a list of IDs called EntryID and each EntryID can belong to ONE table out of a group of six, what is the best way to get a listing of these?
For example:
select r.* from #Reminders r left join mytable1 mt1 on (r.EntryID = mt1.EntryID) left join mytable2 mt2 on (r.EntryID = mt2.EntryID) left join mytable3 mt3 on (r.EntryID = mt3.EntryID) left join mytable4 mt4 on (r.EntryID = mt4.EntryID)
As you can see, #Reminders has one field called EntryID (and many rows).
In my example above, only ONE of those tables will actually be able to join but I have no idea which one has the matching EntryID.
What is the best way for me to do this? I want to grab "ReportStatus" from the corresponding "mytable"... (each "mytable" has a ReportStatus column)
I have a patient record and emergency contact information. I need to find duplicate phone numbers in emergency contact table based on relationship type (RelationType0 between emergency contact and patient. For example, if patient was a child and has mother listed twice with same number, I need to filter these records. The case would be true if there was a father listed, in any cases there should be one father or one mother listed for patient regardless. The link between patient and emergency contact is person_gu. If two siblings linked to same person_gu, there should be still one emergency contact listed.
Below is the schema structure:
Person_Info: PersonID, Person Info contains everyone (patient, vistor, Emergecy contact) First and last names Patient_Info: PatientID, table contains patient ID and other information Patient_PersonRelation: Person_ID, patientID, RelationType Address: Contains address of all person and patient (key PersonID) Phone: Contains phone # of everyone (key is personID)
The goal to find matching phone for same person based on relationship type (If siblings, then only list one record for parent because the matching phones are not duplicates).
I have a strange request that might not be possible based on the laws of relational databases but I thought I'd give it a try.
I have three tables which for simplicity I will call A, B and C. Table A contains my master records, Table B contains user details and the final table contains some extra data
In my initial search when joining A and B, I return 100 records. I then need to search in table C for these 100 records based on a criteria. the expected result should return all 100 rows for the ones that match and also the ones that do not match. The problem is that in Table C, not all the 100 IDs exist, so there will not be a corresponding record. Unfortunately, our users still want to see all 100 records in the output. Is this possible
As always any help or direction would be appreciated.
I've created a website using Microsoft's built-in profile/membership system. I'm used to referring to these elements without actually having to interact with the underlying tables. Thus, I type:Profile.UserName when I want a users UserName rather than querying the database manually.Now, I need to create a scheduled job in SQL that sends emails out based upon user preferences specified under Profile. Is there a way to use Profile within SQL Server 2005?David.
HiI'm trying to make a transaction with the TransactionScope class and if I modify a profile and call ProfileBase.Save(), it throws an exception with the message: "MSDTC is unavailable".thanks
Hi, I want to configure SQL Server 2005 Express to work with profiles and that kind of ASP.NET 2.0 things :) The problem is, when I run aspnet_regsql.exe I can't get a list of databases, and when I manually enter the database name, I get the same error."(provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)" I've enabled the named pipes and tcp/ip connections in the Server Configuration Manager.I've also tried multiple logins. I run the sql server locally.I can connect with my website to it, I can use the Management Studio Express (with win auth). But I can't get those tables configured :( Anybody got an idea? Thanks in advance.
Guys! I'm using SQL Mail to send out Emails to Customers re: outstanding Invoices. I also use SQL Mail on the same box to send out emails to our Sales Reps with summaries of outstanding invoices. Now, I've set up multiple outlook profiles on the server. I tried using
DOES NOT WORK!!! the profile does'nt switch at all. SQL Mail sends out all the emails using the profile that it started with. How can I use xp_startmail and xp_stopmail to switch between different user profiles.
Hi Everyone, I have an e-commerce site that uses Profiles in ASP.NET 2.0 to store items in a shopping cart. The problem is that we are now using LDAP authentication, so we are getting rid of the ASPNETDB membership database that stores the profile values. How can get the shopping cart functionalty to work with SQL Server 2005? How can I store the profile object in a SQL Server instead of the ASP.NET memberships database? Here is the profile field: <profile enabled="true"> <properties> <add name="Cart" serializeAs="Binary" type="Commerce.ShoppingCart" allowAnonymous="true"/> </properties> </profile>
Scenario:DBMS: SQL Server 2000OS: Windows Server 2003Goal:Enable SQLMAIL via Internet MailProblem:How do I set up the MAPI profile for the SQL Server service account?On Windows 2000 Server, I can use "Control Panel"/"Mail" tocreate a MAPI profile for the SQL Server service account, and thenreference that profile for SQLMAIL in SQL Server and SQL Server Agent.But on Windows Server 2003, there is no "Mail" applet in "Control Panel".How do I create a MAPI profile, for the SQL Server service account,on Windows Server 2003?
I tried all the INFORMATION_SCHEMA on SQL 2000 andI see that the system tables hold pretty much everything I aminterested in: Objects names (columns, functions, stored procedures, ...)stored procedure statements in syscomments table.My questions are:If you script your whole database everything you end up havingin the text sql scripts, are those also located in the system tables?That means i could simply read those system tables to get any informationI would normally look in the sql script files?Can i quickly generate a SQL statement of all the indexes on my database?I read many places that Microsoftsays not to modify anything in those tables and not query them since theirstructure might change in future SQL versions.Is it safe to use and rely the system tables?I basically want to do at least fetching of information i want from thesystem tables rather than the SQL script files.I also want to know if it's pretty safe for me to make changes in thesetables.Can i rename an object name for example an Index name, a Stored Procedurename?Can i add a new column in the syscolumns table for a user table?Thank you
Okay I've posted something like this in another area but it doesn't seem to be focused towards the right people. I need ya'll SQL experts! I have a database table that has 5 columns: Time Status Message Source IP The Status column has only two choices "up" or "down" I'm looking for some kind of advanced query that will do the following: I need the query in my application to pull the table and then match up IP, Status, and Message columns. It should only find One match and then once it has found a match it needs to recognize that one is an "up" status and one is a "down" status and then remove them from the database. Does that make sense? Is it impossible?
Hi, This is a where clause I am using in a search. WHERE (ADDRESS_STREET LIKE '%' + @Search + '%' ) I am trying to do a search which returns the most matching record. For example if I have a record with Denver as text . If I search for Denvr the spell error is intended , I will not get the result. How can I create a stored procedure to counter probable spelling errors and return matching results in a ranked order. Thanks
say I have a list from an sql statement (results list) this list contains 10 items
In another table, in one particular column - there is a match for one of these items from the initial list.
SO... this may be the list _____________________ itemnumber 1 2 3 4 5 6 7 8 9 10 ------------------------------
in the other table there is a match... but just for one item on that list. ____________________ othertablefield 11 13 14 3 <------ match 99 78 ----------------------------
Dear all, I have two table, both table have a col which stored the data in CSV format, ie "ab , bc, de", etc I would like to select the item where "any one item" in first table is same as "any one item" on second table. I have alread have the private CSV function which can convert the string and return as table. However, I find i cannot do it like this select ..... where dbo.CSVfunction(tableA.colA) Thx//CSV functiondeclare @separator char(1) set @separator = ',' declare @separator_position int declare @array_value varchar(1000) set @array = @array + ',' while patindex('%,%' , @array) <> 0 begin select @separator_position = patindex('%,%' , @array) select @array_value = left(@array, @separator_position - 1) select @array_value = RTrim(@array_value) select @array_value = LTrim(@array_value) Insert @StringTable Values (Cast(@array_value as varchar(50))) select @array = stuff(@array, 1, @separator_position, '') endre @separator char(1) set @separator = ',' declare @separator_position int declare @array_value varchar(1000) set @array = @array + ',' while patindex('%,%' , @array) <> 0 begin select @separator_position = patindex('%,%' , @array) select @array_value = left(@array, @separator_position - 1) select @array_value = RTrim(@array_value) select @array_value = LTrim(@array_value) Insert @StringTable Values (Cast(@array_value as varchar(50))) select @array = stuff(@array, 1, @separator_position, '') end
My company is going to start a Cancel web site so customers can cancel their future orders by simply filling out a form. I was wondering what would be the best way to compare the cancel db to db that is used to store customer information and order information.
The cancel website will only hold the name, address, and Credit Card Number used. There will not be a customer/order number. The simple Credit Card to Credit Card search will be easy but I was more worried about comparing the rest of the information in case the Credit Card search fail. I was told the "standard" look up was to take the first 3 letters of the first name and the last 3 letters of the last name. If that comes up with more then one record, take that set and see if the zip code matches up and/or part of the street address matches up (counting we do not have very reliable people inputting the information into the system is not going to help this search).
Anyway, any suggestions on comparing the data would be great.
Through out the table any number of the columns can be null.
I wish to remove rows from this table where the columns values are contained in another row i.e Row 2 above is contained within row 3. Similarly, row 3 is contained within row 4. So, the only rows I want from the sample data above are rows 1, 4 and 5.
I hope I have explained my query adequately, and any help would be great appreciated. (Before I go mad...!)
Hi everyone. I'm developing a web search engine using asp and SQL Server 2000. I need to return records that matches with a string entered by users. In example, suppose my database to have this structure and it's filled like this:
ID NAME KEYWORDS --- ------- ----------- AA025 NAME1 attached, atic, common, business, hotels AA026 NAME2 headache, medicin, aspirins, heat, health AA027 NAME3 at, services, music, electronics
suppose that user enters 'at'. By now, i'm using this pattern '%<input_text>%'. So in this example, pattern would be '%at%'. As i remarked in the fields above, the three fields matches, and that's not what i'm looking for. I want that the result of that query be just the last field, with 'AA027' ID. Thanks in advance guys. Cheers.
I'm getting the following error when running dbcc checkdb and no clue as to what to do.
Msg 8992, Level 16, State 1, Line 1 Check Catalog Msg 3853, State 1: Attribute (referenced_major_id=1328111872,referenced_minor_id=5) of row (class=0,object_id=1795589535,column_id=0,referenced_major_id=1328111872,referenced_minor_id=5) in sys.sql_dependencies does not have a matching row (object_id=1328111872,column_id=5) in sys.columns.
What can be done with Sql Server for this problem?
In this query:
select A, B, C from (select A, B, C from table1) as G1, (select A, B, C from table2) as G2
where G1.A = G2.A and G1.B = G2.B and G1.C = G2.C
a record in table1 can be matched with more than one record in table2, and I don't have any other field in the tables that helps me link one G1 record to a specific G2 record. What can I do to match each G1 record to only one (if any) G2 record (if there are two records in table2 which are identical, I want the query to choose one of them and leave the other one unmatched)? Another question: is there a way to count records and attribute them a unique number? What is an index?
hi allI want to write a sotred procedure which has a parameter,called@name.The SQL statement must return all rows where this parameter is asubatring of a special column called FirstName.This procdeure do thesame ,but return all rows where @name is the exact matching.whatshould I do?/*This procedure return the exact matching*/CREATE PROCEDURE substring@name varchar(50)ASSELECT *FROM table1WHERE (table1.firstName LIKE @name);
When I write the above query, I want to exclude the AddIDs that have a Name_Full of DAVIELT. In the Name_Full column the only difference between the two strings is the "T" in DAVIELT.
Please note that it has to use a LIKE statement. The code is already written that way and I do not have control over it.
We are in initial phase of designing an application whose primary purpose is to match a list of loan defaulters with bank's customer database.
Let me make the picture more clearer to you,
There is a list of defaulters which contains about 1500000 names (Primary names and Aliases e.t.c), related to a person.
On the other hand there is database which contains bank's customer's (about 20, 00000 records) data.
We will have to run batch a scan for name matching against defaulters list to the current customer base of bank to check if any of existing customer is in defaulter's list.
This whole exercise of name matching should be finished in 4 to 5 hours (as required by user/client).
How can I optimize my data base design or processing logic to achieve this goal,
Should I use full text query or I should load data related to names in memory than carry out some logic for name match. These are two my primary thoughts.
I have 2 tables called Customer & CustomerArchive. Both Tables have the same Column names and Datatypes etc.
CustomerID Address Postcode
1 10 Alcove Street 4000
50 21 Spring Street 4032
33 5 Terrace Place 4010
What I need to do is compare each column except for the CustomerID to see if it is the same!
In other words, I need to compare Address in the Customer Table with Address in the CustomerArchive Table.
I have to do this with all columns (12) of them between the 2 Tables.
Is there a efficient & smart way to do this aprt from the obvious way of joing the 2 Tables via the CustomerID and performing a Customer.Address <> CustomerArchive.Address Customer.PostCode <> CustomerArchive.PostCode etc etc etc!
