SQLTeam.com | Weblogs | Forums

Sql query for compare


#1

Table 1 has "Gender" field with "Male" and "Female" in it, table 2 has "Gender" field with "M" and "F" in it. write a query to compare data and list the differences.

Thank you,


#2

This sounds like homework of some type. Please provide us your query and we can guide you. But it does you no good for us to do the whole thing for you.


#3

my idea is using case from 1table:
select case gender
when A.Gender='Male' then 'M'
whenA.Gender='Female' then 'F'
from table1 A
but here, I need from 2 tables to check. so, would like to get from team.
thank you


#4

I have tried as below but not reached exactly. could you make changes to it?

SELECT T.*, T.M, t2.Gender
(SELECT LEFT(Gender,1) AS M
FROM T1) T
INNER JOIN table2 t2 ON T.M= t2.Gender

please make corrections for this.


#5

Since you have not provided the table definitions we really cannot help you much.

Hint INNER JOIN Table2 t2 ON LEFT(t1.Gender, 1) = t2.Gender


#6

I don't think you want to join on gender. There is probably some type of id or other key that you join on.

Then maybe compare gender and only list the row(s) if it is different? Is that what you need to do?

SELECT table1.*, table2.*
FROM table1
INNER JOIN table2 on table1.id = table2.id and table1.gender <> table2.gender


#7

Thanks @ScottPletcher I knew that but confessed myself between work and answering. My comment about no definitions was toward that end. So you did a lot better at what I was aiming at. :smile: