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

3...Cow

3...Dog

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

Please show the entire expected output and clearly explain the logic behind it. — Tim Biegeleisen
– Tim Biegeleisen, Commented Mar 11, 2018 at 6:49

Naveen Punjabi · Accepted Answer · 2018-03-11 06:57:01Z

1

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';

answered Mar 11, 2018 at 6:57

Naveen Punjabi

263 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

MKK Over a year ago

Thanks a lot.It really helped.

Stack Overflow · Accepted Answer · 2018-03-12 05:59:03Z

0

You can try with case

like this :

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

edited Mar 12, 2018 at 5:59

answered Mar 11, 2018 at 6:44

Stack Overflow

2,8126 gold badges26 silver badges48 bronze badges

Collectives™ on Stack Overflow

MySQL query to join two table having different columns

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related