CODEWITHSUNDEEP
sqlsqlitejoinwhere-in
Dave Chambers
Dave Chambers

Reputation: 2563

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.

Upvotes: 0

Views: 81

Answers (1)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

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

select p1.delsern,p1.deleactvol,p2.deleactvol
from (
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) p1
join (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) p2
on p1.DELSERN = p2.DELSERN 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 delsern, you can do 

select t2.COMNAME, t0.DELDATE, t1.DELSERN, 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.DELSERN, t3.PRODNAME, t3.PRODEANC
having sum(case when t1.DELEACTVOL < 0 then -1 when t1.DELEACTVOL > 0 then 1 end) = 0

Upvotes: 1

Related Questions