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?
create table a (id int, name varchar(10)); create table b(id int, sal int); insert into a values(1,'John'),(1,'ken'),(2,'paul'); insert into b values(1,400),(1,500);
select * from a cross apply( select max(sal) as sal from b where b.id = a.id)b;
Below is the result for the same:
idname sal 1John500 1ken500 2paulNULL
Now I'm not sure why the record with ID 2 is coming using CROSS APPLY, shouldn't it be avoided in case of CROSS APPLY and only displayed when using OUTER APPLY.
One thing that I noticed was that if you remove the Aggregate function MAX then the record with ID 2 is not shown in the output. I'm running this query on SQL Server 2012.
I'm trying to clean up a database design and I'm in a situation to where two tables need a FK but since it didn't exist before there are orphaned records.
Tables are:
Brokers and it's PK is BID
The 2nd table is Broker_Rates which also has a BID table.
I'm trying to figure out a t-sql statement that will parse through all the recrods in the Broker_Rates table and delete the record if there isn't a match for the BID record in the brokers table.
I know this isn't correct syntax but should hopefully clear up what I'm asking
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 have a client who needs to copy an existing sale. The problem isthe Sale is made up of three tables: Sale, SaleEquipment, SaleParts.Each sale can have multiple pieces of equipment with correspondingparts, or parts without equipment. My problem in copying is when I goto copy the parts, how do I get the NEW sale equipment ids updatedcorrectly on their corresponding parts?I can provide more information if necessary.Thank you!!Maria
Hi I have a table with a user column and other columns. User column id the primary key.
I want to create a copy of the record where the user="user1" and insert that copy in the same table in a new created record. But I want the new record to have a value of "user2" in the user column instead of "user1" since it's a primary key
Sorry for the less then descriptive post title but I didn't find a better way to describe it. I'm developing an app in the express editions of VB and SQLserver. The application is a task/resource scheduler. The main form will have a datepicker or weekly overview and show all tasks scheduled per day. The problem is, I've got one or more people assigned to tasks and I wonder what's the best way to design this. Personally, I'd go for one Task table, a People table and a table that provides a link between them (several record per task, one for each person assigned linking TaskID and PplID). However, I don't see a nice way of showing this data to the end user, allowing him to edit/add etc on ONE screen.
To fix that the only way I see is just add columns to the Task table for every person with select boxes. This way everything can be done on one simple screen. This obviously does present some future issues.
On top of this, which people are available on a day varies and there should be an option to allow a user to set who is available on a specific day. Which would lead me to my first idea and add another table that would provide this. but then I'm having design issues again for the form.
I'm kinda stuck atm, can anyone shed some light on this. I'm sure there is an elegant way of doing this but I'm failing at finding it.
We're running a Sage CRM install with a SQL Server 2000 database at the back end. We're using the Sage web services API for updating data and a JDBC connection to retrieve data as it's so much quicker.
If I retrieve a record using the JDBC connection and then try and update the same record through the web services, the query times out as if the record is locked for updates. Has anyone experienced anything similar or know what I'm doing wrong? If I just use DriverManager.getConnection() to establish the connection instead of the datasource, and then continue with the same code I don't get these record locking problems. Please find more details below.
Thanks, Sarah
The JDBC provider for the datasource is a WebSphere embedded ConnectJDBC for SQL Server DataSource, using an implementation type of 'connection pool datasource'. We are using a container managed J2C authentication alias for logging on.
This is running on a Websphere Application Server v6.1.
I am attempting to create a multi-record file (as described in my last thread) and have found the following set of instructions very helpful: http://vsteamsystemcentral.com/cs21/blogs/steve_fibich/archive/2007/09/25/multi-record-formated-flat-file-with-ssis.aspx
I have been able to create a sample file with two of my record types.
I now need to build on this further, because I have 9 record types in total that need to be extracted to a single flat file.
does anyone have any ideas how I might extend the example above to include more record types or know of another means of achieving this?
Thanks in advance for any help you might be able to provide.
Can anyone advise me as to how I can add the date and time to 2 columns in the sql server database for each record that is added. I'd prefer not to use the webform. Can sql server add the date automatically to the row? thanks
Is that possible to restrict inserting the record if record already exist in the table.
Scenario: query should be
We are inserting a bulk information of data, it should not insert the row if it already exist in the table. excluding that it should insert the other rows.
Hi All,I have a table in SQL Server 2000 that contains several million memberids. Some of these member ids are duplicated in the table, and eachrecord is tagged with a 1 or a 2 in [recsrc] to indicate where theycame from.I want to remove all member ids records from the table that have arecsrc of 1 where the same member id also exists in the table with arecsrc of 2.So, if the member id has a recsrc of 1, and no other record exists inthe table with the same member id and a recsrc of 2, I want it leftuntouched.So, in a theortetical dataset of member id and recsrc:0001, 10002, 20001, 20003, 10004, 2I am looking to only delete the first record, because it has a recsrcof 1 and there is another record in the table with the same member idand a recsrc of 2.I'd very much appreciate it if someone could help me achieve this!Much warmth,Murray
i have a matrix report and i want to switch the record background color with each record in the value column in that matrix report e.g 1st record background color is gray and next record background color is white and then the next record background color is gray ... and so on
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.
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!
I'm working with Access 2,007, and I have an instruccion in SQL but when I execute it, it shows an error "" the instruccion and var in red
Dim Mes As String Dim CostoKw As Double Dim SQLUdate As String Mes = InputBox("Mes a Actualizar Costo: ") CostoKw = InputBox("Costo que se Aplicara: ") Select Case Mes Case 1 DoCmd.RunSQL "UPDATE Clientes SET Clientes.Costo01;" = CostoKw
The table's field Costo01 is the type Number and Double.
Init SC --- 89 Post NCOA --- 89 Post Supp --- 89 Revised Final State Counts --- 89 Revised Final State Counts --- 94 ***********************************************************************************************
Since "Revised Final State Counts" appears in both cycles 89 & 94. How can I query the table so that I only get that 1 record?
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
A column in my database contains phrases such as "Extreme Golf: The Showdown" or "Welcome to Happy Land". I need to write a search engine so that users could type in phrases such as "Golf Extreme Showdown" or "Happy Land" and the correct, or closest matched results will be returned. I don't need variations of words, just phrase keyword match based search. I know I could do this by using multiple LIKE %% statements OR'd together, but this would be too performance intensive. So, I have heard I should use charindex somehow to achieve this in a stored procedeure. Does anyone have any clue how to solve this problem? Thanks!
Hi, i am facing a scenario is that, iam matching table one table form oracle and another table form sql what is want is that using this two tables i want to display unmatched record from the orcale table very urgent help me please send the code for this scenario example in sql table i have fldinvoiceno 00000001 00000002 in oracle table i have fldinvoiceno 00000001 00000002 00000003 so matching the two table i need to get 00000003
Hey all, Got a little problem. have 2 matching tables on different servers with the EXACT same column layout and data (the tables are being replicated with MSSQL7) and one table is 200MB while the other is 2000MB. I'm running MSSQL7 SP2. Any ideas???
i have one task in which i have to match some attributes(required for creating a new databse) with the exiting database, are these attributes present in exisisting database, if yes how many , and how many are not,pls do reply
