MySQL query to join two table having different columns

Question

Consider the below table T1

FLOOR...ANIMAL...COLOUR

1...Cat...Black

1...Dog...Black

1...Cat...White

2...Dog...White

2...Dog...Brown

3...Cow...Brown

3...Dog...White

3...Cat...Black

Now, I with query: Select * from T1 where animal='cat'; got this Table T2

Table T2

FLOOR...ANIMAL...COLOUR

1...Cat...Black

1...Cat...White

3...Cat...Black

The result I want is: All the elements present in Table T2 and only(FLOOR,ANIMAL) of Table T1. Something like the below Table T3:

Table T3

FLOOR...ANIMAL...COLOUR

1...Cat...Black

1...Cat...White

3...Cat...Black

1...Dog

2...Dog

2...Dog

3...Cow

3...Dog

How this Table T3 can be acheived? Any suggestions? Thanks in advance.

Answer 1

You can try with case

like this :

case when ANIMAL = "cat" then COLOUR else '' end

Answer 2

You could try taking the UNION of two TABLES wherein the second TABLE should have its third column as empty string:

select * from T1 where animal = 'cat' UNION select floor, animal, " " as color from T1 where animal <> 'cat';