Table A consists of firstname, secondname, lastname, msisdn, registrationdate.. table b have firstname, secondname, lastname, msisdn, registrationdate and a few other columns - but i only want to consider these mentioned 5 columns. I have something like this
SELECT SELECT A.MSISDN,A.FIRSTNAME,A.SECONDNAME,A.LASTNAME,A.REGDATE, B.MSISDN,B.FIRSTNAME,B.SECONDNAME,B.LASTNAME,B.REGDATE
FROM TABLE1 A
INNER JOIN TABLE2 B ON A.MSISDN = B.MSISDN
WHERE A.FIRSTNAME != B.FIRSTNAME
OR A.LASTNAME != B.LASTNAME
Previously i only considered firstname, lastname from table A and checked for mismatch in table B, but i'm getting thousands of records as results and i wanted narrow down the search.
How do i include an if else case here so that
if a.firstname == b.firstname && a.secondname == b.lastname - Ignore this record.
if a.firstname == b.firstname && a.lastname == b.lastname - Ignore this record.
if a.firstname == b.firstname && a.lastname == b.secondname- Ignore this record.
if a.firstname not equal to b.firstname - show this record as result
if a.firstname == b.firstname && a.secondname not equal to b.lastname - show this record as result
else show all the records as results that doesn't fall into any of these above cases. Also if it is possible, please include a solution to ignore capital letters and small letters while checking for mismatches.
the problem here is, after executing the query from @sagi, in the results i'm getting the rows which has perfect match between first,second and lastnames but has a different registration date - as we are not considering registration date in the query, will it impact the results?
This should do the trick :
SELECT A.*, B.*
FROM TABLE1 A
INNER JOIN TABLE2 B ON A.MSISDN = B.MSISDN
WHERE (UPPER(B.FIRSTNAME),UPPER(B.LASTNAME)) NOT IN ((UPPER(A.FIRSTNAME),UPPER(A.LASTNAME)),(UPPER(A.FIRSTNAME),UPPER(A.SECONDNAME)))
No need for IF
, if this condition returns true, it means all your conditions are met, and either first_name
is different or last_name
is .
You probably want to use a CASE
expression.
They look like this:
SELECT col1, col2, (case when (action = 2 and state = 0)
THEN
1
ELSE
0
END)
as state from tbl1;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With