CODEWITHSUNDEEP
mysqlsql
Ameen Yousuf
Ameen Yousuf

Reputation: 33

How to split data from two columns in one table into multiple columns of a result table

So I have been trying to get mySQL query to work for a large database that I have. I have (lets say) two tables Table_One and Table_Two. Table_One has three columns Type, Anima and TestID and Table_Two has 2 columns Test_Name and Test_ID. Example with values is below:

           TABLE_ONE


Type             Animal        TestID       
-----------------------------------------
Mammal             Goat           1
Fish               Cod            1
Bird               Chicken        1
Reptile            Snake          1
Bird               Crow           2
Mammal             Cow            2
Bird               Ostrich        3

Table_Two
Test_name         TestID       
-------------------------
Test_1              1
Test_1              1
Test_1              1
Test_1              1
Test_2              2
Test_2              2
Test_3              3

In Table_One all types come under one column and the values of all Types (Mammal, Fish, Bird, Reptile) come under another column (Animals). Table_One and Two can be linked by Test_ID

I am trying to create a table such as shown below:

Test_Name      Bird      Reptile      Mammal          Fish
-----------------------------------------------------------------
Test_1         Chicken    Snake        Goat            Cod
Test_2         Crow                    Cow
Test_3         Ostrich

This should be my final table. The approach I am currently using is to make multiple instances of Table_One and using joins to form this final table. So the column Bird, Reptile, Mammal and Fish all come from a different copy of Table_one.

For e.g 

Select
Test_Name As 'Test Case Name',
Table_Bird.Animal AS 'Birds',
Table_Mammal.Animal AS 'Mammal',
Table_Reptile.Animal AS 'Reptile,
Table_Fish.Animal AS 'Fish'
From Table_One
INNER JOIN TABLE_Two AS Table_Bird
On Table_One.TestID = Table_Bird.TestID
INNER JOIN TABLE_Two AS Table_Mammal
On Table_One.TestID = Table_Mammal.TestID
INNER JOIN TABLE_Two AS Table_Reptile
On Table_One.TestID = Table_Reptile.TestID
INNER JOIN TABLE_Two AS Table_Fish
On Table_One.TestID = Table_Fish.TestID
Where Table_Bird.Type LIKE 'Birds'
AND Table_Mammal.Type LIKE 'Mammals'
AND Table_Reptile.Type LIKE 'Reptiles'
AND Table_Fish.Type LIKE 'Fish'

The problem with this query is it only works when all entries for Birds, Mammals, Reptiles and Fish have some value. If one field is empty as for Test_Two or Test_Three, it doesn't return that record. I used Or instead of And in the WHERE clause but that didn't work as well.

I would really appreciate it if someone can point me in the right direction.

Upvotes: 3

Views: 99

Answers (2)

M. Grue
M. Grue

Reputation: 331

You can try and move you WHERE up to the JOIN condition and make it LEFT JOIN instead of INNER JOIN - Like this:

Select
    Test_Name As 'Test Case Name',
    Table_Bird.Animal AS 'Birds',
    Table_Mammal.Animal AS 'Mammal',
    Table_Reptile.Animal AS 'Reptile,
    Table_Fish.Animal AS 'Fish'
From Table_One
LEFT JOIN TABLE_Two AS Table_Bird ON Table_One.TestID = Table_Bird.TestID AND Table_Bird.Type LIKE 'Birds'
LEFT JOIN TABLE_Two AS Table_Mammal ON Table_One.TestID = Table_Mammal.TestID AND Table_Mammal.Type LIKE 'Mammals'
LEFT JOIN TABLE_Two AS Table_Reptile ON Table_One.TestID = Table_Reptile.TestID AND Table_Reptile.Type LIKE 'Reptiles'
LEFT JOIN TABLE_Two AS Table_Fish ON Table_One.TestID = Table_Fish.TestID AND Table_Fish.Type LIKE 'Fish'

Upvotes: 1

user2641570
user2641570

Reputation: 814

Try replacing your INNER JOIN statements by LEFT JOIN.

Upvotes: 2

Related Questions