MYSQL search multiple condition in one column

Question

table1
-------------------------------
| id | color     | shape      |
|------------------------------
|  1 | green     | triangle   |
|  2 | green     | square     |
|  3 | blue      | rectangle  |
|  4 | white     | sphere     |
|  5 | yellow    | triangle   |
-------------------------------

I want to get a result where row has multiple condition focusing in one column. Here is my code.

SELECT * FROM table1 WHERE shape = 'triangle' and shape = 'square';

However, result should have the same value in column color. Is it possible to have a result as below?

-------------------------------
| id | color     | shape      |
|------------------------------
|  1 | green     | triangle   |
|  2 | green     | square     |
-------------------------------

Answer 1

One option uses not exists:

select t.*
from mytable t
where 
    shape in ('triangle', 'square')
    and exists (
        select 1
        from mytable t1
        where 
            t1.shape in ('triangle, 'square')
            and t1.color = t.color
            and t1.shape <> t.shape
    )

If you are running MySQL 8.0, you can also use window functions. Assuming that there are no duplicates in (color, shape) tuples:

select id, color, shape
from (
    select t.*, count(*) over(partition by color) cnt
    from mytable t
    where shape in ('triangle', 'square')
) t
where cnt > 1