how to use multiple And condition where I combined three tables using inner join-CodePudding

SELECT z.name, a.name, a.type, a.gender,
(
    SELECT
    COUNT(a.type)
    FROM animal a
 )
FROM zoo z
INNER JOIN zoo_animal_map m 
ON z.id = m.zoo_id
INNER JOIN animal a 
ON a.id = m.animal_id
WHERE a.type="Tiger" AND a.type ="Elephant" AND a.type =" Leopard";

CodePudding user response：

I think you need IN

SELECT 
  zoo.name   AS zoo_name
, ani.type   AS animal_type
, ani.gender AS animal_gender
, ani.name   AS animal_name
FROM zoo_animal_map AS map
JOIN zoo AS zoo
  ON zoo.id = map.zoo_id
JOIN animal AS ani 
  ON ani.id = map.animal_id
WHERE ani.type IN ('Tiger', 'Elephant', 'Leopard')
ORDER BY zoo.name, ani.type, ani.gender, ani.name

There are no animals that are both tiger AND elephant.
Not sure about the leophants though.

But if you want to find the zoo's that have all 3 types?
Then grouping by zoo, with conditional aggregation might work for you.

SELECT *
FROM
(
    SELECT 
      map.zoo_id
    , zoo.name AS zoo_name
    , COUNT(CASE 
            WHEN ani.type = 'Tiger'
            THEN ani.id
            END) AS Tigers
    , COUNT(CASE 
            WHEN ani.type = 'Elephant'
            THEN ani.id
            END) AS Elephants
    , COUNT(CASE 
            WHEN ani.type = 'Leopard'
            THEN ani.id
            END) AS Leopards
    , COUNT(CASE 
            WHEN ani.type = 'Tiger' 
             AND ani.gender LIKE 'F%' 
            THEN ani.id
            END) AS FemaleTigers
    , COUNT(CASE 
            WHEN ani.type = 'Elephant' 
             AND ani.gender LIKE 'F%' 
            THEN ani.id
            END) AS FemaleElephants
    , COUNT(CASE 
            WHEN ani.type = 'Leopard'
             AND ani.gender LIKE 'F%' 
            THEN ani.id
            END) AS FemaleLeopards
    , COUNT(DISTINCT ani.type) AS AnimalTypes
    FROM zoo_animal_map AS map
    JOIN zoo AS zoo
      ON zoo.id = map.zoo_id
    JOIN animal AS ani 
      ON ani.id = map.animal_id
    GROUP BY map.zoo_id, zoo.name
) AS zoos
WHERE Tigers > 0
  AND Elephants > 0
  AND Leopards > 0
ORDER BY zoo_name

zoo_name	animal_type	animal_gender	animal_name
The Wild Zoo	Elephant	Male	adam
The Wild Zoo	Leopard	Male	allen
The Wild Zoo	Tiger	Female	nancy
The Wild Zoo	Tiger	Male	tommy

zoo_id	zoo_name	Tigers	Elephants	Leopards	FemaleTigers	FemaleElephants	FemaleLeopards	AnimalTypes
1	The Wild Zoo	2	1	1	1	0	0	4

Demo on db<>fiddle here

CodePudding user response：

Your problem is not related to the joins, you have three AND conditions which are contradicting.
Just replace it with OR and it will work