too long

table "animals":

| id | category | category_id |
|:---|---------:|:-----------:|
| 1  |   horses |     1       |    
| 2  |   horses |     2       |
| 3  |     dogs |     1       |

table "horses":

| id | name  | 
|:---|------:|
| 1  | james |    
| 2  |  john |   
| 3  |  alan |    

table "dogs":

| id | name   | 
|:---|-------:|
| 1  |   lara |    
| 2  | freddy |   
| 3  |  puppy |    

I want to get the names from different tables based on a category:

 $pdo = $db->prepare('SELECT *
     FROM animals 
     LEFT JOIN dogs ON animals.category_id = dogs.id
     LEFT JOIN horses ON animals.category_id = horses.id
  ');

while ($row = $pdo->fetch(PDO::FETCH_ASSOC)) {
 echo "Category: ".$row["category"].", Name: ".$row["name"];
}

The result I would need:

Category: Horses, Name: James
Category: Horses, Name: John
Category: Dogs, Name: Lara

My problem is now, how can I define, that if a category is "horses" I want to get data only from "horses", and if a category is "dogs" I want to get data only from "dogs". Because here I get a conflict when the "category_id" has the same value.

One-to-One connection

I would recommend changing your tables around:

Table animals:

| id | name | category_id |
|:---|-----:|:-----------:|
| 1  | John |      1      |
| 2  | Mary |      1      |
| 3  |  Rex |      2      |

Table categories:

| id |  type |
|:---|------:|
| 1  | Horse |
| 2  |   Dog |
| 3  |   Cat |

(put a 'unique' index on the type field; this way you can never enter the same type more than once)

Then use the following query (Personally, I would include the 'animals.id' just to make things easier for your code)

SELECT animals.id, animals.name, categories.type as category
FROM animals
LEFT JOIN animals.category_id = categories.id

This way you would get the data you want:

| id | name | category |
|:---|-----:|:---------|
| 1  | John | Horse    |
| 2  | Mary | Horse    |
| 3  |  Rex | Dog      |

This way, you have a one-to-one connection from the animals to the categories table.

One-to-Many connections

If you want to include, say.. times that they have been fed, you could create a one-to-many connection by adding 2 more tables:

Table animals_feedings (this is a link-table):

| id | animals_id | feedings_id |
|:---|:----------:|:-----------:|
| 1  |      1     |      1      |
| 2  |      1     |      2      |
| 3  |      1     |      5      |
| 4  |      3     |      3      |
| 5  |      2     |      4      |
| 6  |      2     |      2      |
| 7  |      2     |      5      |

(in order to speed up later selections, I would recommend putting an index on each of the two *_id columns for later use)

Table feedings:

| id | comment    |
|:---|:-----------|
| 1  | 1 apple    |
| 2  | grass      |
| 3  | dry food   |
| 4  | 2 apples   |
| 5  | hay        |

(put a 'unique' index on the comment field; this way you can never enter the same comment more than once & put an index on the comment field as well (to speed up the INSERT IGNORE INTO-query once this has thousands or millions of records), just make sure it's a char field, not varchar (not 100% sure if this still matters, but char has a fixed size and thus can be more predictable for the indexing))

That way, you can store your comments in a normalized way (the best of inserting these is by a "INSERT IGNORE INTO feedings"-query so you don't end up wasting space on duplicate comments)

To get this data, you can simple do this:

SELECT animals.id,
       animals.name,
       categories.type as category,
       TRIM(GROUP_CONCAT(' ', feedings.comment)) as feedings
FROM animals
LEFT JOIN categories ON animals.category_id = categories.id
LEFT JOIN animals_feedings ON animals.id = animals_id
LEFT JOIN feedings ON animals_feedings.feedings_id = feedings.id
GROUP BY animals.id

or using aliases to make your SQL query a bit more readable:

SELECT a.id,
       a.name,
       c.type as category,
       TRIM(GROUP_CONCAT(' ', f.comment)) as feedings
FROM animals a
LEFT JOIN categories c ON a.category_id = c.id
LEFT JOIN animals_feedings af ON a.id = af.animals_id
LEFT JOIN feedings f ON af.feedings_id = f.id
GROUP BY a.id

This way you order the animal list and feeding comment combined into one field:

| id | name | category | feedings             | 
|---:|:-----|:---------|:---------------------| 
|  1 | John | Horse    | hay, grass, 1 apple  | 
|  2 | Mary | Horse    | hay, grass, 2 apples | 
|  3 | Rex  | Dog      | dry food             | 

Understanding how to normalize your data is very important, especially when your databases are being put to use in applications that will be used for many years.

I hope this helps!

First of all, your tables are inconsistent. Your category_id should not have same id for both horses and dogs.

Anyways, if changing schemas is not an option, then you can use following workaround :

Select * from
(select *from animals where category = 'horses') animals
Left Join horses on animals.category_id = horses.id

union all

Select * from
(select *from animals where category = 'dogs') animals
Left Join dogs on animals.category_id = dogs.id

Hope it helps!