February 21, 2017 at 2:49 pm
I have two sets of data .
Select * from table A where fileid=3196
select * from table A Where fileid=2918
example : There are 534 rows with fileid 3196 generated and some 100 rows for fileid 2918.
and some of the rows are common in both the sets and how do we compare column by column?
February 21, 2017 at 3:02 pm
When you say compare column by column, does that mean ALL columns? Are the number of columns; and the names thereof; in both result sets, the same?
If so, you can do two comparisons rather easily:
Select -- list all fields in table A here except for fileid
from table A
where fileid=3196
EXCEPT
select -- list all fields in table A here except for fileid
from table A
Where fileid=2918
The other compare is to just reverse the order of the queries.
Steve?(aka sgmunson)?:) 🙂 :)?
Health & Nutrition
Rent Servers for Income (picks and shovels strategy)
February 21, 2017 at 3:11 pm
sgmunson - Tuesday, February 21, 2017 3:02 PMWhen you say compare column by column, does that mean ALL columns? Are the number of columns; and the names thereof; in both result sets, the same?
If so, you can do two comparisons rather easily:
Select -- list all fields in table A here except for fileid
from table A
where fileid=3196
EXCEPT
select -- list all fields in table A here except for fileid
from table A
Where fileid=2918The other compare is to just reverse the order of the queries.
this is not giving what i am wanted. It does not show records which are not common to both sets.
The table A has all columns generated by fileid . Columns names same but may have different values. I am trying to get the data which is not same from both the sets . And what column values does not match.
February 21, 2017 at 3:18 pm
This is the best that we can do given the limited information that you have given us. If you want better answers, you'll have to provide more details about exactly what you are trying to do. See the first link in my signature for more details.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
February 21, 2017 at 3:29 pm
drew.allen - Tuesday, February 21, 2017 3:18 PMThis is the best that we can do given the limited information that you have given us. If you want better answers, you'll have to provide more details about exactly what you are trying to do. See the first link in my signature for more details.Drew
I have table which loads the files with id , so column names are same but values in those columns may be different or may be same for those file id's. So, I am wanting to find what are the records a that are different and what column values causing it different
SELECT [First Name], [Last Name], [Product Name],Quantity , Qualitytype
FROM TABLE A
Where FileID=31650
SELECT [First Name], [Last Name], [Product Name],Quantity , Qualitytype
FROM TABLE A
Where FileID=29531
February 21, 2017 at 3:42 pm
komal145 - Tuesday, February 21, 2017 3:29 PMdrew.allen - Tuesday, February 21, 2017 3:18 PMThis is the best that we can do given the limited information that you have given us. If you want better answers, you'll have to provide more details about exactly what you are trying to do. See the first link in my signature for more details.Drew
I have table which loads the files with id , so column names are same but values in those columns may be different or may be same for those file id's. So, I am wanting to find what are the records a that are different and what column values causing it different
SELECT [First Name], [Last Name], [Product Name],Quantity , Qualitytype
FROM TABLE A
Where FileID=31650SELECT [First Name], [Last Name], [Product Name],Quantity , Qualitytype
FROM TABLE A
Where FileID=29531
But how are you matching records to determine which columns are different? You need some type of key (either natural or surrogate) to say that records from the two different Files "match". Without that, you can only determine that the record as a whole either does or does not exists in the other file.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
February 21, 2017 at 4:11 pm
Guessing here, but I think you want a FULL OUTER JOIN on Last Name, First Name and Product Name (QualityType is a possibility too I suppose). You can filter after that if you like for things not equal or missing on either side of the set.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 22, 2017 at 11:08 am
komal145 - Tuesday, February 21, 2017 3:29 PMSo, I am wanting to find what are the records a that are different and what column values causing it different
Different from what? Sometimes you sound like you're comparing rows then other times columns. Are you looking for rows in the second set that don't completely match some row from the first set? Are you looking for rows that match 2 columns and want to know if other columns for those match? The better you can describe your problem the better chance you have of getting a helpful answer on here.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply