Dave Chambers Dave Chambers - 6 months ago 10
SQL Question

How to compose two SQLITE queries with different conditions to return a pair of rows

I have some delivery data, let's say for UK supermarkets. The data I am interested in is composed in two

select
queries as follows:

select t2.COMNAME, t0.DELDATE, t1.DELSERN, t1.DELEACTVOL, t3.PRODNAME, t3.PRODEANC from TDELIVER t0 inner join TDELENTR t1 on (t1.DELSERN=t0.DELSERN)
inner join TCOMPANY t2 on (t2.COMSERNO=t0.COMSERNO)
inner join TPRODUCT t3 on (t1.PRODSERN=t3.PRODSERN)
where t1.DELEACTVOL < 0


Which yields results where
DELEACTVOL
is negative, something like this, but with many more entries and real data:

Negative Volumes

select t2.COMNAME, t0.DELDATE, t1.DELSERN, t1.DELEACTVOL, t3.PRODNAME, t3.PRODEANC from TDELIVER t0 inner join TDELENTR t1 on (t1.DELSERN=t0.DELSERN)
inner join TCOMPANY t2 on (t2.COMSERNO=t0.COMSERNO)
inner join TPRODUCT t3 on (t1.PRODSERN=t3.PRODSERN)


Which yields all results where
DELEACTVOL
is negative or positive, something like this:

All Volumes

As you can see, the two queries are identical aside from the fact one filters out (the majority) of positive volume entries. Each delivery has a unique
DELSERN
.

I need a query that will give me each
DELSERN
(every delivery), such as the
DELSERN
XXXX.......89
, delivered to Tesco on May 24th, which features some product, say
Product A
, with a
negative delivery volume
ONLY if there is in the same delivery a
positive delivery volume
for that product. In the sample data
XXXX.......37
should not be returned.

I have a feeling I should be using
WHERE IN
or
JOIN
but I don't know.

I have tried:

select t2.COMNAME, t0.DELDATE, t1.DELEACTVOL, t3.PRODNAME, t3.PRODEANC from TDELIVER t0 inner join TDELENTR t1 on (t1.DELSERN=t0.DELSERN)
inner join TCOMPANY t2 on (t2.COMSERNO=t0.COMSERNO)
inner join TPRODUCT t3 on (t1.PRODSERN=t3.PRODSERN)
where t1.DELEACTVOL < 0
and EXISTS(select p2.COMNAME, p0.DELDATE, p1.DELEACTVOL, p3.PRODNAME, p3.PRODEANC from TDELIVER p0 inner join TDELENTR p1 on (p1.DELSERN=p0.DELSERN)
inner join TCOMPANY p2 on (p2.COMSERNO=p0.COMSERNO)
inner join TPRODUCT p3 on (p1.PRODSERN=p3.PRODSERN)
where p1.DELEACTVOL > 0 and t1.DELSERN = p1.DELSERN)
ORDER by t1.DELEACTVOL


... hoping that
and t1.DELSERN = p1.DELSERN
part would do the trick but this returns the same as the the first query. I guess because EACH delivery which a negative volume for Product A would have at least SOME positive products? I probably need to take into account
PRODNAME
too?

Any help would be great.

vkp vkp
Answer

You can join the queries on all columns except deleactvol and select the required columns.

select p1.delesern,p1.deleactvol,p2.deleactvol
from (
select t2.COMNAME, t0.DELDATE,t1.DELESERN, t1.DELEACTVOL, t3.PRODNAME, t3.PRODEANC 
from TDELIVER t0 
inner join TDELENTR t1 on (t1.DELSERN=t0.DELSERN) 
inner join TCOMPANY t2 on (t2.COMSERNO=t0.COMSERNO)
inner join TPRODUCT t3 on (t1.PRODSERN=t3.PRODSERN) 
where t1.DELEACTVOL < 0) p1
join (select t2.COMNAME, t0.DELDATE,t1.DELESERN, t1.DELEACTVOL, t3.PRODNAME, t3.PRODEANC 
      from TDELIVER t0 
      inner join TDELENTR t1 on (t1.DELSERN=t0.DELSERN) 
      inner join TCOMPANY t2 on (t2.COMSERNO=t0.COMSERNO)
      inner join TPRODUCT t3 on (t1.PRODSERN=t3.PRODSERN) 
      where t1.DELEACTVOL > 0) p2
on p1.DELESERN = p2.DELESERN and p1.COMNAME = p2.COMNAME 
and p1.DELDATE = p2.DELDATE and p1.PRODNAME = p2.PRODNAME and p1.PRODEANC = p2.PRODEANC 

If there can only be one positive and negative deleactvol per delesern, you can do

select t2.COMNAME, t0.DELDATE, t1.DELESERN, t3.PRODNAME, t3.PRODEANC 
from TDELIVER t0 
inner join TDELENTR t1 on (t1.DELSERN=t0.DELSERN) 
inner join TCOMPANY t2 on (t2.COMSERNO=t0.COMSERNO)
inner join TPRODUCT t3 on (t1.PRODSERN=t3.PRODSERN) 
group by t2.COMNAME, t0.DELDATE, t1.DELESERN, t3.PRODNAME, t3.PRODEANC
having sum(case when t1.DELEACTVOL < 0 then -1 when t1.DELEACTVOL > 0 then 1 end) = 0