CODEWITHSUNDEEP
mysql
mth0mas
mth0mas

Reputation: 3

MYSQL Query: find related entries from same table matching a condition determined through a second table

I'm new here, thanks for having me :)
Was hoping to get ideas for a solution to the following: I have a database for scientific specimens and experiments. Each specimen was tested at a certain combination of parameters. Parameters are varied in discrete steps (for example parameter 1: 2, 4 or 6 volts; parameter 3: 2, 10 or 20 amps). A group of specimens make up one experiment.

CREATE TABLE specimens (
    id      INTEGER   PRIMARY KEY,
    parameter1    INTEGER   not null,
    parameter2    INTEGER   not null,
    parameter3    INTEGER   not null,
    experiment_id   INTEGER   not null
);

CREATE TABLE experiments (
    id      INTEGER   PRIMARY KEY
);

INSERT INTO `experiments` (`id`) VALUES
    (1),
    (2),
    (3);

INSERT INTO `specimens` (`id`, `parameter1`, `parameter2`, `parameter3`, `experiment_id`) VALUES
    (1, 2, 2, 2, 1),
    (2, 2, 2, 10, 1),
    (3, 2, 2, 20, 1),
    (4, 2, 2, 2, 2),
    (5, 4, 2, 2, 2),
    (6, 6, 2, 2, 2),
    (7, 2, 2, 2, 3),
    (8, 4, 2, 10, 3),
    (9, 6, 2, 20, 3);

I'm trying to put together a query that does the following:

Select all specimens from the database, for which there exists at least one other specimen within the same experiment, that was tested at a different level of a certain factor.

For example:

I'm (obviously) not a pro at this, but so far I figured it can't be done with JOIN, as I need to vertically append rows to my set of results, but it can't be a UNION query either, because the second query can't interact with the first one. My current progress is something like:

SELECT specimens1.*
FROM specimens AS specimens1
WHERE 
specimens1.id = 2
OR specimens1.id IN (
    SELECT specimens2.id
    FROM specimens AS specimens2
    WHERE specimens2.experiment_id = specimens1.experiment_id
)

Which turned out to be nonsense. I hoped it would at least get me specimens 1, 2 and 3 for a start, as they belong to the same experiment. It doesn't, though. Also, I have no idea how to implement the "at least one other level of a certain factor within the same experiment" condition.

Does anyone have a hint? Thank you for your time!

Cheers, Max

Upvotes: 0

Views: 42

Answers (2)

mth0mas
mth0mas

Reputation: 3

Thanks a lot, P.Salmon! From your answer I was able to construct the following, which gets the result I actually had in mind:

SELECT * FROM specimens
HAVING FIND_IN_SET(id, (SELECT group_concat(gc)
FROM
    (SELECT 'parameter1' parameter)
    a LEFT JOIN
    (
        SELECT e.id,'parameter1' AS parameter, GROUP_CONCAT(s.id) gc
        FROM experiments e
        JOIN specimens s ON s.experiment_id = e.id
        GROUP BY e.id
        HAVING COUNT(DISTINCT parameter1) > 1
    )
    b ON b.parameter = a.parameter
)) > 0

I was looking for the actual data rows of all specimens meeting one distinct-parameter-criterion at a time, rather than a full set of all possible results. Example above is for parameter1 only, then.

Upvotes: 0

P.Salmon
P.Salmon

Reputation: 17640

A union does help as does group concat and left joining all parameters.

DROP TABLE IF EXISTS SPECIMENS,EXPERIMENTS;

CREATE TABLE specimens (
    id      INTEGER   PRIMARY KEY,
    parameter1    INTEGER   not null,
    parameter2    INTEGER   not null,
    parameter3    INTEGER   not null,
    experiment_id   INTEGER   not null
);

CREATE TABLE experiments (
    id      INTEGER   PRIMARY KEY
);

INSERT INTO `experiments` (`id`) VALUES
    (1),
    (2),
    (3);

INSERT INTO `specimens` (`id`, `parameter1`, `parameter2`, `parameter3`, `experiment_id`) VALUES
    (1, 2, 2, 2, 1),
    (2, 2, 2, 10, 1),
    (3, 2, 2, 20, 1),
    (4, 2, 2, 2, 2),
    (5, 4, 2, 2, 2),
    (6, 6, 2, 2, 2),
    (7, 2, 2, 2, 3),
    (8, 4, 2, 10, 3),
    (9, 6, 2, 20, 3);

select a.parameter, group_concat(gc) vals from
(
(select 'parameter1' parameter union select 'parameter2' union all select 'parameter3') a   
left join
(
select e.id,'parameter1' as parameter,group_concat(s.id) gc
from   experiments e
join   specimens s on s.experiment_id = e.id
group by e.id having count(distinct parameter1) > 1  
union all
select e.id,'parameter2' as parameter,group_concat(s.id) gc
from   experiments e
join   specimens s on s.experiment_id = e.id
group by e.id having count(distinct parameter2) > 1  
union all
select e.id,'parameter3' as parameter,group_concat(s.id) gc
from   experiments e
join   specimens s on s.experiment_id = e.id
group by e.id having count(distinct parameter3) > 1 
) b on b.parameter = a.parameter
) 
group by a.parameter;

+------------+-------------+
| parameter  | vals        |
+------------+-------------+
| parameter1 | 4,5,6,7,8,9 |
| parameter2 | NULL        |
| parameter3 | 1,2,3,7,8,9 |
+------------+-------------+
3 rows in set (0.102 sec)

Upvotes: 1

Related Questions