None None - 12 days ago 6
MySQL Question

Add another condition to the where dependeing on case result

So I've been thinkng how to solve this but i can't figure out it yet.
I have a table 'A' with 3 fields 'id', 'origin' and 'description', origin can be repeated but description must be unique for each origin,
what I'm tryng to do is that if description has the key "PT" and is the only row associated with its origin key then it must not be showed in the resultset,
otherwise it must be showed with the other(s) description field.

select * from a
where 1 = 1
and case when (select count(*) from a where 1 = 1 group by origin) > 1
then description != 'PT' else 1 = 1 end


For example, I have 5 keys for origin O1, O2, O3, O4, O5. O1 has associated two descriptions AC and PT and O2 has associated only one description which is
PT, in this case in the resultset only two rows will be showed, and they belong to the O1 key, because O2 has only one key.

This is what I've tried by far, but it won't work the way I want, so I was wondering if you can point the direction to accomplishes this.

I tried to use sqlfiddle but is not working somehow, this is what I used to create and populate the table.

CREATE TABLE A
(`id` int, `origin` varchar(7), `description` varchar(255))
;

INSERT INTO A
(`id`, `origin`, `description`)
VALUES
(1, 'O1', 'AC'),
(2, 'O1', 'PT'),
(3, 'O2', 'PT'),
(3, 'O3', 'MI'),
(3, 'O3', 'PT'),
(3, 'O4', 'EG'),
(3, 'O4', 'PT'),
(3, 'O5', 'PT')
;

Answer

If I understand you correctly, a simple embedded query will work.

It seems like you're looking for rows which contain an origin value that exists without a PT description?

Query:

SELECT *
FROM A
WHERE origin IN (SELECT origin FROM A WHERE description != 'PT')

Output:

+------+--------+-------------+
| id   | origin | description |
+------+--------+-------------+
|    1 | O1     | AC          |
|    2 | O1     | PT          |
|    3 | O3     | MI          |
|    3 | O3     | PT          |
|    3 | O4     | EG          |
|    3 | O4     | PT          |
+------+--------+-------------+
Comments