SQL cut results from table [closed]

This question is unlikely to help any future visitors; it is only relevant to a small geographic area, a specific moment in time, or an extraordinarily narrow situation that is not generally applicable to the worldwide audience of the internet. For help making this question more broadly applicable, visit the help center. Closed 11 years ago.

I have the following query:

    SELECT cust_id,cust_name,SUM(calls.duration) as duration
  FROM 
      calls, telephone

  WHERE calls.from_t_no = telephone.t_no
  AND
  calls.duration <=60

  group by telephone.cust_id, telephone.cust_name

Which give the following results;

cust_id cust_name duration
0123456789  Avi 18
1234567890  Benny   27
2345678901  Gadi    13
3456789012  Dalia   69
4567890123  Hilla   5
5678901234  Varda   14
7890123456  Haim    20
8901234567  Tali    20
9012345678  Yoram   46

I also have this query:

SELECT cust_id,cust_name,SUM(calls.duration) as duration 
      FROM calls ,telephone
      WHERE to_t_no = telephone.t_no
      AND
      calls.duration >=50开发者_Go百科0
      group by telephone.cust_id, telephone.cust_name

Which provides the following results:

cust_id cust_name   duration
2345678901  Gadi    50022
4567890123  Hilla   50000

the second query is actually another filter or condition which the first query should match. I cannot include the second query within the first query because it uses different columns. I need to "subtract" the second query from the first query.

How do I do that?

---

If you truly can't merge the queries, you can JOIN the results like this:

SELECT T0.cust_id, T0.cust_name, T0.duration FROM

(  SELECT cust_id,cust_name,SUM(calls.duration) as duration
FROM 
    calls, telephone

WHERE calls.from_t_no = telephone.t_no
AND
calls.duration <=60

group by telephone.cust_id, telephone.cust_name) T0

JOIN

(SELECT cust_id,cust_name,SUM(calls.duration) as duration 
  FROM calls ,telephone
  WHERE to_t_no = telephone.t_no
  AND
  calls.duration >=500
  group by telephone.cust_id, telephone.cust_name) T1

ON T0.cust_id = T1.cust_id

---

Try to post the complete structure of your table and describe what you really need.

If i understand correctly the problem the conditions that you are exposing are mutually exclusive. I think that there is not possible that any record match both conditions because the final query will look like this:

SELECT cust_id,cust_name,SUM(calls.duration) as duration 
FROM calls ,telephone
WHERE 
    to_t_no = telephone.t_no
    AND calls.from_t_no = telephone.t_no
    AND calls.duration <=60
    AND calls.duration >=500
group by telephone.cust_id, telephone.cust_name;

If what you want is a general solution to make a "query over a query" you can use this kind of sentence:

SELECT field1, field2 FROM
    (SELECT field1, field2, ..., fieldn FROM table WHERE condition) AS filteredTable
WHERE anyCondition;