Finding Mismatched Rows Between Identical Tables Based On 2 Or More Cols
Jun 8, 2007
CREATE TABLE [RS_A] (
[ColA] [varchar] (10)
[ColB] [int] NULL
)
CREATE TABLE [RS_B] (
[ColA] [varchar] (10)
[ColB] [int] NULL
)
INSERT INTO RS_A
VALUES ('hemingway' , 1)
INSERT INTO RS_A
VALUES ('vidal' , 2)
INSERT INTO RS_A
VALUES ('dickens' , 3)
INSERT INTO RS_A
VALUES ('rushdie' , 4)
INSERT INTO RS_B
VALUES ('hemingway' , 1)
INSERT INTO RS_B
VALUES ('vidal' , 2)
I need to find all the rows in A which do not exist in B
by matching on both ColA and ColB
so the output should be
dickens 3
rushdie 4
So if i write a query like this , I dont get the right result set
SELECT A.ColA, A.ColB
FROMRS_A A
INNERJOIN RS_B B
ONA.ColA <B.ColA
ORB.ColB <B.ColB
But if i do the following, i do get the right result, but following
seems convoluted.
SELECT A.ColA, A.ColB
FROMRS_A A
WHERE ColA + CAST(ColB AS VARCHAR)
NOT IN (SELECT ColA+CAST(ColB AS VARCHAR) FROMRS_B B)
View 6 Replies
ADVERTISEMENT
Jul 30, 2007
Does anyone have a good query that would return records from two tables that are found in one, but not it the other table? In my situation I have 2 tables that are duplicate tables and I need to find an additional 3000 records that were added to one of the tables. I also have a composite key so the query would have col1, col2 and col3 as the composite key. So far I have tried concatenating the 3 columns and giving the result an alias and then trying to show the ones that were not in both tables, but have been struggling. Thanks..
View 4 Replies
View Related
Mar 13, 2008
Here's what I'd like to be able to do: I have a queue that holds any number tasks, so something like this here:
queue_1 task_a
task_b
task_c
task_d
Workers are assigned to teams, Red team, Blue team, Green team. What I need to do is identify instances where all tasks for a given queue have been handled by one team. Once a task has been assigned to a queue any team can work on it, but when only one team has completed every task in a queue a bonus should be awarded.
I'm looking for this to award a bonus:
queue_num task_num team
queue_1 task_a red
task_b red
task_c red
task_d red
No bonus for any team here
queue_num task_num team
queue_1 task_a red
task_b blue
task_c red
task_d green
So the red team earns a bonus. Now, I have thousands of queues each containing any number of tasks. Using T-SQL how can I find all queues where only one team was responsible for completeing every task assigned to the queue? Do I have to use a cursor and eval each task coming through or is there a faster, more efficient way to handle this in SQL?
View 4 Replies
View Related
Nov 8, 2013
i have 'Privious' table as
***Metrics Count***
AccessibilityFilter 1679
Archives 80
Corel 90
DateFilter 1680
and 'Current' table as
***Metrics Count***
AccessibilityFilter 1679
Archives 85
Corel 100
DateFilter 1680
I want to fetch only the mis matched records as below.
***Metrics PreviousCount CurrentCount***
Archives 80 85
Corel 90 100
View 3 Replies
View Related
Apr 23, 2008
I have 2 tables with 2 fields in common between them. How do I get a count of rows in table 1 but not in table 2 matching those 2 fields? Thanks.
View 7 Replies
View Related
Nov 21, 2007
I have a form to assign JOB SITES to previously created PROJECT. The JOB SITES appear in the DataList as it varies based on customer. It can be 3 to 50 JOB SITES per PROJECT.
I have "PROJECT" table with all necessary fields for project information and "JOBSITES" table for job sites. I also created a new table called "PROJECTSITES" which has only 2 columns: "ProjectId" and "SiteId".
What I am trying to do is to insert multiple rows into that "PROJECTSITES" table based on which checkbox was checked. The checkbox is located next to each site and I want to be able to select only the ones I need. Btw the Datalist is located inside of a formview and has it's own datasource which already distincts which JOBSITES to display.
Sample:
ProjectId - SiteId
1 - 5
1 - 9
1 - 16
1 - 18
1 - 20
1 - 27
1 - 31
ProjectId stays the same, only values for SiteId are being different.
I hope I explaining it right. Do I have to use some sort of loop to go through the automatically populated DataList records and how do I make a multiple inserts to database table? We use SQL Server 2005 and VB for code behind. Please ask if I missed on some information. Thank you in advance.
View 10 Replies
View Related
Oct 29, 2006
Hello,I ended up with two identical rows in one table. They should have differences but I cannot update one, as it tries to update both of them, or throws an error. How to update only one row, and leave other as is?
View 10 Replies
View Related
Jun 19, 2004
I am a beginner in Ms-Sql,so kindly help me with this query:-
Following is the table:-
Name Phone email
John 4564 john@abc.com
John 4564 john@abc.com
John 4564 john@abc.com
John 4564 john@abc.com
How can i manage to delete only 2 rows out of these 4 rows
View 11 Replies
View Related
Dec 4, 2007
I'm using a query to see how many times an action was recorded on a person. The query works, it returns this:
John Smith 1
John Smith 1
John Smith 1
Jane Doh 1
Jane Doh 1
Al Johnson 1
but I need it to return totals like this
John Smith 3
Jane Doh 2
Al Johnson 1
This is the query I am using:
Select Player.First_Name, Player.Last_Name, COUNT(Action.Employee_ID)
from Player INNER JOIN
PlayerVisit on PlayerVisit.Player_ID = Player.Player_ID
join Treatment on Treatment.Visit_ID = PlayerVisit.Visit_ID
join Action on Treatment.Action_ID = Action.Action_ID
group by Player.First_Name, Player.Last_Name, Action.Employee_Id;
View 4 Replies
View Related
Mar 13, 2008
I have an "insert into" statement that creates two identical rows in a table, with this statement:
delete from [table] where [column] = @parameterINSERT INTO [table]([fields]) VALUES ([parameter values])
This is the code-behind that performs the insert:
Dim dbConn As New SqlConnection(strConn)Dim cmd As New SqlCommand("sp_CreateUser", dbConn)cmd.CommandType = Data.CommandType.StoredProcedurecmd.Parameters.AddWithValue("@UserID", strUserID)cmd.Parameters.AddWithValue("@UserName", strUserName)cmd.Parameters.AddWithValue("@Email", strEmail)cmd.Parameters.AddWithValue("@FirstName", strFirstName)cmd.Parameters.AddWithValue("@LastName", strLastName)cmd.Parameters.AddWithValue("@Teacher", strTeacher)cmd.Parameters.AddWithValue("@GradYr", lngGradYr)Using dbConndbConn.Open()cmd.ExecuteNonQuery()dbConn.Close()cmd.Dispose()dbConn.Dispose()End Using
I wonder if it inserts twice due to a postback issue. Is there a way to stop two rows from being created in the first place with the same "insert into" statement? I'd appreciate any advice.
View 3 Replies
View Related
Sep 28, 2007
I am trying to create an exception report that will show the difference between two versions of the same row. (Combination of two different sources in sql, with source 1 having childID = 0 and the other source having childID = 1; parentID is the link between them)
The results are as follows:
ParentID - ChildID - Col1 - Col2 - Col3
1 - 0 - AA - BB - CC
1 - 1 - AA - BF - CC
2 - 0 - GG - NN - TT
2 - 1 - DE - NN - TA
3 - 0 - etc
3 - 1 - etc
4 - etc
How can I render the differences in red in RS?
View 1 Replies
View Related
Dec 20, 2007
I need to transform Foxpro table to SQL Server table with merging all rows into one where all column values are the same except one . For this the only column with the different values , I want them also to be merged as coma or space delimited string. The question whether SSIS is a good candidate for this kind of data munging and also would be interested to know knowing as many as possible ways of doing that. Surely I may produce Foxpro script in 5 minutes which wil do that and be a pre-processor action before SSIS starts.
View 3 Replies
View Related
Feb 18, 2015
I have a database with three different tables having the exact same fields. New records are written to table1, before moving to table2 and ultimately table3. I was wondering if it's possible to run the same query on all three tables at the same time. I need to get all unique instances in the JC field from each table after a specified date. I get an "Ambiguous column name" error on the JC and TimeID fields.
SELECT distinct [JC]
FROM [table1], [table2], [table3]
where timeid > '20090900';
View 1 Replies
View Related
Apr 2, 2008
Hi,
I have two tables named Tab1 and Tab2. Both are identical in structure. The only diff is Tab2 has two more additional fields (FromDate and ToDate).
The structure is like below :
Col1
Col2 (Date field)
Col3
Col4
Also Tab 2 have
Col5 (From Date)
Col6 (To Date)
Now I want to transfer some set of reocrds from Tab1 to Tab2. The additional Tab2 field (Col5 and Col6) values should be the minimum and maximum values of Tab1 date field for the current set.
How to accomplish this? Kindly help me in this regard.
Thanks
Somu
View 1 Replies
View Related
Nov 18, 2005
I have two different tables... one for all Staff, and another for all Temp Staff. I need both to output to a datagrid, and so I need to grab both tables from a SQL query to output to my datagrid, but I can't seem to get the logic right for it to work. Can someone give me some suggestions on why my results are blank when I'm running this query? I thought a simple join would allow both sets of identical column names to coexist in peace...SELECT TOP 100 PERCENT dbo.StaffDirectory.UserName, dbo.StaffDirectory.LastName, dbo.StaffDirectory.FirstName, dbo.StaffDirectory.Dept, dbo.StaffDirectory.Title, dbo.StaffDirectory.EMail, dbo.StaffDirectory.LocationFROM dbo.StaffDirectory INNER JOIN dbo.TempStaff ON dbo.StaffDirectory.Location = dbo.TempStaff.Location AND dbo.StaffDirectory.EMail = dbo.TempStaff.Email AND dbo.StaffDirectory.Title = dbo.TempStaff.Title AND dbo.StaffDirectory.Dept = dbo.TempStaff.Dept AND dbo.StaffDirectory.FirstName = dbo.TempStaff.FName AND dbo.StaffDirectory.LastName = dbo.TempStaff.LName AND dbo.StaffDirectory.UserName = dbo.TempStaff.UName AND dbo.StaffDirectory.MDNo = dbo.TempStaff.MDNoIs something wrong here? It just doesn't work =(Any suggestions would be really appreciated.Thank you
View 5 Replies
View Related
May 23, 2007
Hi all,
I have two tables A and B on two both tables have similar architectures bout contain some deferent records.
I like to compare them and find and view the differences in records.
Thanks for any help.
Abrahim
View 3 Replies
View Related
Oct 29, 2014
Selecting the data from these two tables? The columns 'host_ext_id' have the same data BUT in 'adrmst' all data is preceeded by A0000.
table is 'adrmst'
Host External IDAddress NameAddress Line 1Address Line 2City StatePostal Code
A000042401 T-3803VC 1530401 00 WENGER STTOPEKA KS66609
A000042402 CO INC 960 PP TOMLIN MILL RDSTATESVILLENC286258332
A000042403 CO INC 1420 PP BLVD GARYSBURGNC278319748
A000042405 CO INC 1419 PP BROWN RD KISSIMMEEFL347463415
A000042405 CO INC 962 PP COMMERCE DRVALDOSTAGA316011206
table is 'shipment'
Shipment IDHost External IDcar_move_idP_DEST_LOC_ID
42401 42401
42402 42402 SDQD_00862TAGSDQD
42403 42403 SDQD_00863TAGSDQD
42404 42404 SDQD_00863TAGSDQD
42405 42405 SDQD_00863TAGSDQD
View 4 Replies
View Related
Dec 12, 2007
I'd like to extend a package functionality.
I created it drag/drop way with hard-coded table names.
Now for the same source and destination connections I'd like somehow in a loop transform 20 source tables of the same structure to 20 destination tables of the same structure providing table names in a loop.
I also have in the package preparation SQL tasks such as dropping destination table if exists, and then re-creation , so it needs to consume a table name as parameter from my loop.
Is it doable ?
View 6 Replies
View Related
Oct 31, 2007
Im wondering if it is possible to write a procedure that check two identical tables for any missing records. The table design is excatly the same, but some records (of the 40,000) have not copied over to the second table.
Any help would be great, cheers.
View 3 Replies
View Related
Oct 25, 2005
We have written an application which splits up our customers data intotheir individual databases. The structure of the databases is thesame. Is it better to create the same stored procedures in eachdatabase or have them in one central location and use the sp_executesqland execute the generated the SQL statement.Thank you.Mayur Patel
View 4 Replies
View Related
Jan 30, 2008
I'm trying import 7 tables from each of 30 SQL2005 databases into a SQL2005 Consolidation database. I can simply create data flow tasks for each one but instead I would like loop through a list instead.
I've created a table to house the names of the databases from which I want to import the data.
I've created SQL task to return the database names from the table as a "Full Result Set".
I've assigned the result set to a user variable (type = Object) an named the result name 0
What I'd like to do is create a data flow task which connects to each of the databases and imports 7 specified tables from each database appending the table name with my database name in the result set.
I'm stuck on how I'd set the connection strings in my OLE DB Source in my Data Flow task. Any insight would be greatly appreciated.
Thanks in advance.
Bill Webster
View 4 Replies
View Related
Dec 30, 2005
Sorry if this is a super-basic question...I'm used to join selects but not sure how to approach an append select (or something like it)
I have two tables with identical field structures: a Master table with 10,000 rows and a Custom table table with 1,000 rows
To keep it simple, let's say the two tables each have a FirstName field and a LastName Field.
Is it possible to use a View or a Select statement (or any other method) to 'append' the rows of both tables so that the result set still has only the two columns (FirstName and LastName) and has 11,000 rows?
Thanks for your help!
Randy
View 4 Replies
View Related
Apr 30, 2007
I am replicating an 80GB database between NY can CT and would like toknow why table sizes are different between the two.Here is an example of sp_spaceused::NY IOI_2007_04_23 rows(279,664) reserved(464,832)data(439,960) index_size(24,624)CT IOI_2007_04_23 rows(279,666) reserved(542,232)data(493,232) index_size(48,784)Thanks,
View 1 Replies
View Related
Apr 2, 2008
Hi, I need to write a query which I have never attempted before and could do with some help.... I have a Groups table and a Users_Groups look up table. In this model, users can only be assigned to 1 group. If a group is deleted, a trigger should fire and delete any rows in User_Groups having a matching Groups.Ref. Unfortunately, the trigger hasn't been firing and I now have a load of defunct rows in Users_Groups relating users to groups which do not exist.I now need to find all of these defunct rows in Users_Groups so that I can delete them. How can I find rows in Users_Groups where the parent rows and refs in Groups are null? I've tried searching the net for something similar but don't even know how to word the search properly to get any half relevant results. Cheers PS, I do realise I need to tighten the constraints on my database
View 5 Replies
View Related
May 8, 2008
I have the following variables VehicleID, TransactDate, TransactTime, OdometerReading, TransactCity, TransactState.
VehicleID is the unique vehicle ID, OdometerReading is the Odometer Reading, and the others are information related to the transaction time and location of the fuel card (similar to a credit card).
The records will be first grouped and sorted by VehicleID, TransactDate, TransactTime and OdometerReading. Then all records where the Vehicle ID and TransactDate is same for consecutive rows, AND TransactCity or TransactState are different for consecutive rows should be printed.
I also would like to add two derived variables.
1. Miles will be a derived variable that is the difference between consecutive odometer readings for the same Vehicle ID.
2. TimeDiff will be the second derived variable that will categorize the time difference for a particular vehicle on the same day.
My report should look like:
VehID TrDt TrTime TimeDiff Odometer Miles TrCity TrState
1296 1/30/2008 08:22:42 0:00:00 18301 000 Omaha NE
1296 1/30/2008 15:22:46 7:00:04 18560 259 KEARNEY NE
Can someone please help me here?
Thanks,
Romakanta
View 1 Replies
View Related
Nov 6, 2015
I have 2 tables A, B with 1 to many relationship
Table A(ProductID), TableB(ProductID, FileID)
I need to find only the records in Table A that may have more than one FileIDs in Table B, since some ProductIDS have multiple FileIDs in Table B...
View 8 Replies
View Related
Jul 9, 2015
I have a scenario here where the data looks like -
ID Date
100 07/01
100 07/02
100 07/03
100 08/01
100 08/02
100 08/15
Now I need to find out unique occurrences of ID - 100 ( where count = unique only if the occurrences are in consecutive days, gap of even 1 day causes it to be a different instance ) - SO with the above data I should have unique occurrences as 3.efficient way to calculate this ?
View 5 Replies
View Related
Jun 25, 2007
When I open a table and need to make a change within a particular record...
How do I find the particular row that I am looking for? I seem to have to scroll through 600 records to find the one I need. I've found a search and replace function but it doesn't seem to work.
Thanks I appreciate any advice you can give me.
View 3 Replies
View Related
Mar 25, 2002
I'm stuck. I have a table that I want to pull some info from that I don''t know how to.
There are two colomuns, one is the call_id column which is not unique and the other is the call_status column which again is not unique. The call_status column can have several values, they are ('1 NEW','3 3RD RESPONDED','7 3RD RESOLVED','6 PENDING','3 SEC RESPONDED','7 SEC RESOLVED').
i.e example, this is the existing data.
Call_id Call_Status
555555 3 3RD RESPONDED
235252 7 SEC RESOLVED
555555 7 3RD RESOLVED
325252 6 PENDING
555555 6 PENDING
325235 3 SEC RESPONDED
555555 1 NEW
This is the data I want...
Call_id Call_Status
555555 3 3RD RESPONDED
555555 6 PENDING
555555 7 3RD RESOLVED
The call_id could be any number, I only want the 6 PENDING rows where there are other rows for that call_id which have either 3 3RD RESPONDED or 7 3RD RESOLVED. If someone knows how it would be a great help.
Cheers,
Chris
View 1 Replies
View Related
Feb 9, 2005
Can this be done easilly through a stored proc?
View 1 Replies
View Related
Nov 2, 2015
I have a split string function that will take a comma delimited string and give back a table with all the values.I have a table that has a column with a comma delimited comma delimited list of states.
I can use a where clause to find one state in the table (such as all records that have CA in the states string).But need to find out how to find all the rows that have all or any of the states from a comma delimited parameter.Here is the schema
CREATE FUNCTION [dbo].[split] (@list nvarchar(MAX))
RETURNS @tbl TABLE (svar nvarchar(10) NOT NULL) AS
BEGIN
DECLARE @pos int,
@nextpos int,
@valuelen int
[code]....
View 9 Replies
View Related
Jul 14, 2006
Hi guys,This might be simplest thing, but I am newbie to databases.I need to find out only rows modified within certain time period from adatabase. As I undertand a way out could be adding an where clause forthe time period might be an option, I might be wrong here again.But, wanted to know is there any other option. Can triggers or anyother things help me in this matter.Regards,Abhijeet
View 1 Replies
View Related
Jun 24, 2004
Hi, does anyone know how to retrieve mistmatched records across 2 tables.
To clarify, I have table A with 1175 records and Table B with 894 records.
The records from table A match exactly some the records in table B.
I want to create another table with the extra 281 records from Table A which does not match that of Table B.
I have tried the query with Select where fields_1.a<>fields_2.b AND fields_2.a<>fields_2.b etc
but that doesn't seem to work.
*desparate*
View 2 Replies
View Related