CODEWITHSUNDEEP
sqlfirebird
Mike Fly
Mike Fly

Reputation: 1

SQL select rows with combination of other rows parameters

I have table with first column as ID (not unique) and only one parameter in second column. There can be more rows with the same ID and different parameter and I need to select ID with logical combination of parameters

For example select only ID for which there is a parameter "A" and ("B" or "C") or at least select only ID with parameters "A" and "B".

DB is in Firebird, but it is no problem to convert it to MySQL if there is no other way.

Edit:

Table:

+----+-----------+
| ID + PARAMETER |
+----+-----------+
|  1 |         A |
|  1 |         B |
|  2 |         B |
|  3 |         A |
|  3 |         C |
+----------------+

I need to get each ID which have PARAMETER A and B = ID 1 or ID with PARAMETER A and (B or C) = ID 1,3

is it clear now?

Upvotes: 0

Views: 1315

Answers (3)

Abecee
Abecee

Reputation: 2393

Reverting to MySQL. After creating a view

CREATE VIEW ParameterList AS
  SELECT
    id
    , GROUP_CONCAT(DISTINCT parameter ORDER BY parameter SEPARATOR ',') pList
  FROM SomeTable
  GROUP BY id
;

you could:

SELECT
  id
FROM ParameterList
WHERE pList = 'A,B'
;

SELECT
  id
FROM ParameterList
WHERE pList IN ('A,B', 'A,C')
;

See it in action: SQL Fiddle.

You need, of course, to be strict in formulating your queries: The to be checked for items must be arranged in line with the view's
GROUP_CONCAT(DISTINCT parameter ORDER BY parameter SEPARATOR ',')
regarding both ORDER and SEPARATOR.

Please comment, if and as this does require adjustment / further detail.

Upvotes: 1

ain
ain

Reputation: 22749

I quess you could just join the table to itself using ID, "once per condition", ie to get IDs which have PARAMETER A and B:

SELECT distinct a.ID
FROM tab a
join tab b on(a.id = b.id)
where a.PARAMETER = 'A' and b.PARAMETER = 'B'

To get IDs with PARAMETER A and (B or C)

SELECT distinct a.ID
FROM tab a
join tab b on(a.id = b.id)
where a.PARAMETER = 'A' and (b.PARAMETER = 'B' or b.PARAMETER = 'C')

Upvotes: 0

shawnt00
shawnt00

Reputation: 17915

MySQL devs seem to hate subqueries but it's really the proper way to do this. I may not have gotten the conditions exactly as you require them but it should be close. And I think the A and B condition is redundant with A and (B or C).

select distinct ID
from T t1
where exists (
    select 1
    from T t2
    where t2.ID = t1.ID
    group by t2.ID
    having
            count(case when t2.parameter = 'A' then 1 else null end) > 0
        and count(case when t2.parameter in ('B', 'C') then 1 else null end) > 0
)

Since A is required in all cases then you might find a performance improvement in doing it this way but it's a little less generic.

select distinct ID
from T t1
where t1.parameter = 'A' and exists (
    select 1
    from T t2
    where t2.ID = t1.ID and t2.parameter in ('B', 'C')
)

Upvotes: 2

Related Questions