CODEWITHSUNDEEP
sqlpostgresql
anon
anon

Reputation:

Select from database by different column values in rows

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'

Upvotes: 0

Views: 65

Answers (2)

Thorsten Kettner
Thorsten Kettner

Reputation: 95101

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;

Upvotes: 1

etsa
etsa

Reputation: 5060

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")

Upvotes: 1

Related Questions