Select from database by different column values in rows

Question

ID        ID_ENTITY        ID_TICKET       C_ENUM        VAL2       VAL3
 1              680           460910        quack
 2              680           460910                    65536           
 3              680           460910                                text
 4              680           460909        quack
 5              680           460909                    65536           
 6              680           460909                                text
 7              680           460908        quack
 8              680           460908                    65535           
 9              680           460908                                text

I have SQL to get "ID_TICKET" where "C_ENUM" = 'quack':

select "ID_TICKET"
from "T_TICKET_TYPE_ENTITY_VALUE"
where "ID_ENTITY" = 680 and "C_ENUM" = 'quack'

But I need to get all "ID_TICKET" where "C_ENUM" = 'quack' AND, for example, VAL2 = 65536.

Something like this:

select "ID_TICKET"
from "T_TICKET_TYPE_ENTITY_VALUE"
where "ID_ENTITY" = 680 and "C_ENUM" = 'quack' and "VAL2" = 65536

How can I do this?

Full query which I'm trying to do:

select t."ID"
from "T_TICKET" t
join "T_TICKET_TYPE_ENTITY_VALUE" entv on entv."ID_TICKET" = t."ID"
join "T_TICKET_TYPE_ENTITY" ent on ent."ID" = entv."ID_ENTITY"
where t."ID_STATUS" != 13 and ent."ID_TICKET_TYPE" = 462
and entv."C_ENUM" = 'quack'

just remove ID_ENTITY from the where criteria. select "ID_TICKET" from "T_TICKET_TYPE_ENTITY_VALUE" where "C_ENUM" = 'quack' — Asdfg
– Asdfg, Commented Oct 11, 2017 at 11:59
"But I need to get all "ID_TICKET" where "C_ENUM" = 'quack' AND, for example, VAL2 = 65536." Rephrase it, is not really clear what should be your filtering condition. Probably do you want group by C_ENUM, won't you? — Carmine Tambascia
– Carmine Tambascia, Commented Oct 11, 2017 at 12:02

etsa · Accepted Answer · 2017-10-11 12:08:04Z

One way is using EXISTS: the first two conditions are to select the id you desire, the last is to extract all ID_TICKETs):

SELECT *
from "T_TICKET_TYPE_ENTITY_VALUE" A
WHERE EXISTS (SELECT 1 
              FROM "T_TICKET_TYPE_ENTITY_VALUE" B 
              WHERE B."ID_ENTITY" = 680 and B."C_ENUM" = 'quack' 
                    AND A."ID_TICKET"=B."ID_TICKET");

Output:

ID_TICKET   ID_ENTITY   C_ENUM
1           680         quack
2           200         quick
3           680         quack

Sample data:

INSERT INTO "T_TICKET_TYPE_ENTITY_VALUE" VALUES (1,680,'quack');
INSERT INTO "T_TICKET_TYPE_ENTITY_VALUE" VALUES (1,200,'quick');
INSERT INTO "T_TICKET_TYPE_ENTITY_VALUE" VALUES (2,600,'quack');
INSERT INTO "T_TICKET_TYPE_ENTITY_VALUE" VALUES (3,680,'quack');

Your complete query should be:

select t."ID"
from "T_TICKET" t
join "T_TICKET_TYPE_ENTITY_VALUE" entv on entv."ID_TICKET" = t."ID"
join "T_TICKET_TYPE_ENTITY" ent on ent."ID" = entv."ID_ENTITY"
where t."ID_STATUS" != 13 and ent."ID_TICKET_TYPE" = 462
    AND EXISTS (SELECT 1 
                from "T_TICKET_TYPE_ENTITY_VALUE" B 
                where B."C_ENUM" = 'quack' AND entv."ID_TICKET"=B."ID_TICKET")

Thorsten Kettner · Accepted Answer · 2017-10-11 12:54:51Z

0

Strange way of storing data. Anyway, simply aggregate per ticket ID:

select id_ticket
from tickets
group by id_ticket
having max(c_enum) = 'quack'
   and max(val2) = 65536;

answered Oct 11, 2017 at 12:54

Thorsten Kettner

96.6k8 gold badges56 silver badges82 bronze badges

Collectives™ on Stack Overflow

Select from database by different column values in rows

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related