How to write this in SQL?

Question

I have one table called Visit and another called Measurement. Primary key on Visit is vis_id and it is also foreign key in table Measurement. Now I would like to write a report based on data in table Measurement on following conditions:

Visit has field "itemAmount". I want only have data that has itemAmount = 2 in table Visit and both rows in Measurement related to Visit have measurement1 and measurement2 not null in table Measurement. If either one has either measurement1 or measurement2 NULL, then neither of the rows should be included.

How to do this? I know how to join and how to write simple conditions in where clause but I have no idea how to test this "couple feature", ie to test that BOTH rows related to Visit have both measurement1 and measurement2 different from null.

Answer 1

as a condition use:

WHERE itemAmount = 2
  AND measurement1 IS NOT NULL
  AND measurement2 IS NOT NULL

(if i understood you correctly)

Answer 2

Assuming your sample data is as you specified:

Visit:
vis_id      itemAmounts
----------- -----------
1           2
2           1

Item:
item_id     vis_id
----------- -----------
1           1
2           1
3           2

Measurement:
item_id     measurement1 measurement2
----------- ------------ ------------
1           10           20
2           5            10
3           5            5

the query should be this one:

select  m1.*
from    Measurement m1
join    Item i1
    on  m1.item_id = i1.item_id
join    Visit v
    on  v.vis_id = i1.vis_id
    and v.itemAmounts = 2
join    Item i2
    on  v.vis_id = i2.vis_id
    and i1.item_id <> i2.item_id --//take the other item
join    Measurement m2
    on  i2.item_id = m2.item_id
    and m2.Measurement1 IS NOT NULL
    and m2.Measurement2 IS NOT NULL
where   m1.Measurement1 IS NOT NULL
    and m1.Measurement2 IS NOT NULL

or this one:

SELECT  m1.*
FROM    Measurement m1
JOIN    Item i
    ON  m1.item_id = i.item_id
JOIN   (SELECT      v.vis_id
        FROM        Visit v
        INNER JOIN  Item i
                ON  i.vis_id = v.vis_id
        INNER JOIN  Measurement m
                ON  m.item_id = i.item_id
                AND m.Measurement1 IS NOT NULL
                AND m.Measurement2 IS NOT NULL
        WHERE       v.itemAmounts = 2
        GROUP BY    v.vis_id
        HAVING      COUNT(*) = 2
        ) v_filter
    ON  i.vis_id = v_filter.vis_id

Both produce following result:

item_id     measurement1 measurement2
----------- ------------ ------------
1           10           20
2           5            10

Is that what you want?