user3132858 user3132858 - 6 months ago 88
SQL Question

MySQL Multiple SELECT queries with different WHERE clause on the same table

I need some help on a MySQL query that I am trying to build.

The is the table I have:

+----+-----------+---------+-----------+---------+------------+-----------+
| id | fruit | color | shape | country | vit | city |
+----+-----------+---------+-----------+---------+------------+-----------+
| id | Apple | red | spherical | UK | A | London |
| id | Orange | orange | circular | USA | C | New York |
| id | Apple | green | multiple | Mexico | C | Cancun |
| id | Apple | red | spherical | Canada | B | Ottawa |
+----+-----------+---------+-----------+---------+------------+-----------+


Now I want to use one query to perform multiple
SELECT
queries like so:

SELECT * from tbl_fruits WHERE fruit = "Apple" AND vit = "A" AND city = "London";
SELECT * from tbl_fruits WHERE fruit = "Apple" AND vit = "B" AND city != "London;
SELECT * from tbl_fruits WHERE fruit = "Apple" AND vit != "A" AND vit != "B" AND city != "London";


The resulting table would be:

+----+-----------+---------+-----------+---------+------------+-----------+
| id | fruit | color | shape | country | vit | city |
+----+-----------+---------+-----------+---------+------------+-----------+
| id | Apple | red | spherical | UK | A | London |
| id | Apple | green | multiple | Mexico | C | Cancun |
| id | Apple | red | spherical | Canada | B | Ottawa |
+----+-----------+---------+-----------+---------+------------+-----------+


In order for all of this to make sense as to why, I need to get all results for Apple with the first
SELECT
limiting on three conditions. Then I need to get all results for Apple with the second
SELECT
but this time limiting only on two conditions AND excluding the results from the first
SELECT
and so on.

Any idea how to achieve this? I tried several different queries, including
UNION
but somehow cannot get it to display the results I need.

Answer

Use select union and not in

SELECT * from tbl_fruits WHERE fruit = "Apple" AND vit = "A" AND city = "London"
union
SELECT * from tbl_fruits WHERE fruit = "Apple" AND vit = "B" AND city != "London
union
SELECT * from tbl_fruits WHERE fruit = "Apple" AND vit NOT IN ( "A" , "B" ) AND city != "London";
Comments