0

I am trying to subtract the results of the following two SQL queries and am unable to figure it out. I have tried NOT IN and EXCEPT but I think I am doing it incorrectly. Here are my queries:

SELECT DISTINCT first_name, last_name, rating, email, phone
FROM users
WHERE rating !='6';


SELECT DISTINCT first_name, last_name, rating, email, phone
FROM users
JOIN user_transactions
ON users.id=user_transactions.user_id
WHERE rating != '6' AND created_on >= '2014-09-01' AND created_on <= CURDATE();
AdamMc331
  • 16,492
  • 10
  • 71
  • 133
Francisco Cervera
  • 69
  • 7
  • 2
    I'm a little confused, what are your expected results here? – AdamMc331 Nov 18 '14 at 21:58
  • 1
    Yeah, it's unclear what you mean by "subtract the results"? are you trying to delete from the table? are you trying to find all the things that match the first query, but not the second? A table of sample data and a sample result-set that matches what you'd like the results to be, would be extremely useful here. – Taryn East Nov 18 '14 at 22:02
  • The first one outputs a large database, say a few thousand people. The second one is a subset of the first output. I can't directly query the second one, but I need to first compare them and filter out the subset. The results would be just a subset of the first with 5 different columns. – Francisco Cervera Nov 18 '14 at 22:04

2 Answers2

0

It seems that what you're really trying to do here is get all rows where the rating is not equal to 6, and the created_on date is BEFORE 09/01/2014. If I am right, you can do that all in one query:

SELECT DISTINCT first_name, last_name, rating, email, phone
FROM users u
JOIN user_transactions ut
   ON u.id = ut.user_id
WHERE rating != 6 AND created_on < '2014-09-01';

I made that assumption because you are taking all rows where the rating is not 6, and want to get rid of the rows where the created_on date is between 09/01 and now. Is that not the same as selecting all rows where the created date is before 09/01?

AdamMc331
  • 16,492
  • 10
  • 71
  • 133
  • That doesn't give me the results I'm looking for. I'm trying to a subset of the first query. For example, the first query gives me all the people that do not have a rating of 6. The second query gives me all the people that have current history. I want to find the people that do not have a rating of 6 and do not have current history. – Francisco Cervera Nov 18 '14 at 22:08
  • @FranciscoCervera Well, what is your cutoff date for 'current history'? Can the date in the above query be changed to give you what you want? – AdamMc331 Nov 18 '14 at 22:08
  • I've been trying to figure that out and can't seem to find the right mix. – Francisco Cervera Nov 18 '14 at 22:10
  • Well without a definite answer of *exactly* what you're looking for, it would be impossible to write the query. However, I think it's likely that you can use the above query and modify it by adding the necessary condition to the WHERE clause. – AdamMc331 Nov 18 '14 at 22:14
0

You can do what you want with aggregation and a having clause:

SELECT first_name, last_name, rating, email, phone
FROM users u JOIN
     user_transactions ut
     ON u.id = ut.user_id
WHERE rating <> '6';
GROUP BY first_name, last_name, rating, email, phone
HAVING SUM(created_on >= '2014-09-01' AND created_on <= CURDATE()) = 0;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786