July 22, 2015 at 8:01 am
Hi,
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, Animal 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_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.
July 22, 2015 at 8:24 am
Why do you have repeated values on Table_Two? That would only create a great amount of rows when creating the queries.
A better way to do this is by using a cross tab.
SELECT
t2.Test_Name AS 'Test_Name',
MAX( CASE WHEN t1.Type = 'Bird' THEN t1.Animal END) AS 'Birds',
MAX( CASE WHEN t1.Type = 'Mammal' THEN t1.Animal END) AS 'Mammal',
MAX( CASE WHEN t1.Type = 'Reptile' THEN t1.Animal END) AS 'Reptile',
MAX( CASE WHEN t1.Type = 'Fish' THEN t1.Animal END) AS 'Fish'
FROM Table_One AS t1
INNER JOIN TABLE_Two AS t2 ON t1.TestID = t2.TestID
GROUP BY t2.Test_Name
July 22, 2015 at 8:28 am
You should not store your values as shown on the result of the query. That goes against normalization and will give you problems when you need to add a different category (bugs, amphibians, etc).
July 22, 2015 at 10:49 am
hmm tried this approach but kept on getting an error.....
I however was able to solve the problem. I used Left Outer Join instead of Inner Join and moved the WHERE conditions with the joins. This worked!
Thanks for the help.
July 22, 2015 at 11:12 am
ameen.yousuf88 (7/22/2015)
hmm tried this approach but kept on getting an error.....I however was able to solve the problem. I used Left Outer Join instead of Inner Join and moved the WHERE conditions with the joins. This worked!
Thanks for the help.
What error did you get?
Your method will read the table multiple times instead of once.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply