September 17, 2015 at 4:58 pm
HI all,
Table 1 has "Gender" field with "Male" and "Female" in it, table 2 has "Gender" field with "M" and "F" in it. a query to compare data and list the differences.
Thank you,
September 17, 2015 at 5:51 pm
Table 1 has "Gender" field with "Male" and "Female" in it, table 2 has "Gender" field with "M" and "F" in it. a query to compare data and list the differences.
You would have to do something like this...
SELECT a.field1, a.field2, a.field3, a.GenderAbbrev, t2.Gender
(SELECT MyTable.fields
,LEFT(Gender,1) AS GenderAbbrev
FROM MyTable) a
INNER JOIN table2 t2 ON a.GenderAbbrev = t2.Gender;
September 17, 2015 at 6:13 pm
Thank you, Let me try.
September 17, 2015 at 6:33 pm
First, welcome to SQLServerCentral. Please note the link in my signature line for the best way to get help here...
I don't know exactly what you are looking for but here's a few queries that may help you.
-- How to check if genders are the same
DECLARE @table1 TABLE (personID int, gender varchar(10));
DECLARE @table2 TABLE (personID int, gender varchar(10));
INSERT @table1 VALUES (1,'male'),(2,'female'),(3,'female');
INSERT @table2 VALUES (1,'m'),(2,'f'),(3,'m');
SELECT
t1.personID,
t1.gender,
t2.gender,
diff = CASE WHEN LEFT(t1.gender,1) = t2.gender THEN '=' ELSE '!=' END
FROM @table1 t1
JOIN @table2 t2 ON t1.personID = t2.personID;
GO
-- how to find records that don't match in both tables
DECLARE @table1 TABLE (personID int, gender varchar(10));
DECLARE @table2 TABLE (personID int, gender varchar(10));
INSERT @table1 VALUES (1,'male'),(2,'female'),(3,'female'),(4,'male');
INSERT @table2 VALUES (1,'m'),(2,'f'),(3,'m'),(5,'m')
SELECT personID
FROM
(
(
SELECT personID, diff = LEFT(gender,1)
FROM @table1
EXCEPT
SELECT personID, gender
FROM @table2
)
UNION ALL
(
SELECT personID, gender
FROM @table2
EXCEPT
SELECT personID, diff = LEFT(gender,1)
FROM @table1
)
) X
GROUP BY personID
GO
-- another approach
DECLARE @table1 TABLE (personID int, gender varchar(10));
DECLARE @table2 TABLE (personID int, gender varchar(10));
INSERT @table1 VALUES (1,'male'),(2,'female'),(3,'female'),(4,'male');
INSERT @table2 VALUES (1,'m'),(2,'f'),(3,'m'),(5,'m')
SELECT
personID = ISNULL(t1.personID,t2.personID),
t1Gender = LEFT(t1.gender,1),
t2Gender = t2.gender,
tableDiff =
CASE
WHEN t1.personID IS NULL THEN 'No matching personid in table 1'
WHEN t2.personID IS NULL THEN 'No matching personid in table 2'
ELSE 'Exists in both tables '
END +
ISNULL
(
CASE
WHEN LEFT(t1.gender,1)=t2.gender THEN ', genders match'
WHEN LEFT(t1.gender,1)<>t2.gender THEN ', genders dont match'
END, ''
)
FROM @table1 t1
FULL JOIN @table2 t2 ON t1.personID = t2.personID;
GO
-- Itzik Ben-Gan 2001
September 17, 2015 at 11:47 pm
Yes, one of your answer is what am looking for. Thank you.
🙂
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply