CODEWITHSUNDEEP
mysql
Rickson
Rickson

Reputation: 127

How can I compare two fields from two different tables with mysql?

Something just like this

select 
(
case when     
( (select t1.name from table_1 as t1)=(select t2.name from table_2 as t2) )    
then 'same'    
else 'different'     
end
);

I want to compare 'name' from table_1 with the 'name' from table_2 and if they are equals it shows in the field "same name" and if not, it shows "not the same".

There's any kind of case (or anything else) that I can do that?

I will have to do this with multiple fields (name, last name, age, etc...), and I just can't figure this simple query out.

Here's another comparison that i want but now with two columns:

    Table A:
 ID - NAME - Age
 1    rick   12
 2    denis  21
 3    jane   34
 4    john   38
 5    jerry  76
 6    max    54

    Table B:
 ID - NAME - Age
 1    rick   10
 2    jay    21
 3    morris 34
 4    john   38
 5    jerry  79
 6    morty  54

Result from comparing each field with the condition that i want:

ID -   Name     -  Age
1     Same        Different
2     Different   Same
3     Different   Same
4     Same        Same
5     Same        Different
6     Different   Same

Upvotes: 0

Views: 1639

Answers (2)

Rickson
Rickson

Reputation: 127

So in my case, it was needed to have a relation between tables, so there it goes:

Table 1

create table table1 (
    id int auto_increment,
    name varchar(40),
    age int,
    primary key (id)
    );

Table 2

create table table2 (
    id int auto_increment,
    name varchar(40),
    age int,
    primary key (id),
    CONSTRAINT `id` FOREIGN KEY (`id`) REFERENCES `table1` (`id`)
);

Insert some data in both tables...

insert into table1 (name,age) values
('morty',14),
('rick',70),
('beth',35);

insert into table2 (name,age) values
('morty',14),
('rick',50),
('beeth',35);

Query that i wanted:

(select  t1.name as t1name,t2.name as t2name,
 (case
  when t1.name = t2.name then 'same' else 'dif'
 end) as resultName, t1.age as age1,t2.age as age2,
 (case
  when t1.age = t2.age then 'same' else 'dif'
 end) as resultAge
 from table1 as t1
 left join table2 as t2 on t1.id = t2.id
 )
  order by t1name,t2name;

Result:

t1name  t2name  resultName    age1  age2  resultage
beth    beeth   dif           35    35    same
morty   morty   same          14    14    same
rick    rick    same          70    50    dif

Upvotes: 0

Satish Gupta
Satish Gupta

Reputation: 333

try this will help 

 SELECT CASE 
             WHEN t2.name IS NULL THEN 'not the same' 
             ELSE 'same name' 
         END 
    FROM table_1 t1 
    LEFT JOIN table_2 t2 ON t1.name = t2.name

Upvotes: 1

Related Questions