CODEWITHSUNDEEP
mysql
BigDX
BigDX

Reputation: 3547

MySQL query to select rows where one column matches and one column does not

Here are my table setups

Results_class

CREATE TABLE appfilter.results_class (
 activity varchar(120) NOT NULL,
 class varchar(255) NOT NULL,
 PRIMARY KEY (activity, class)
)

Users

CREATE TABLE appfilter.user (
user varchar(15) NOT NULL,
name varchar(100) NOT NULL,
activity_full varchar(200) NOT NULL,
activity varchar(125) NOT NULL,
class varchar(125) NOT NULL
)

The results i want is simple. I want to see all rows where activity matches and class does not.

Example

Results_class

activity                        class
com.google.android.apps.plus    com.google.android.apps.circles.realtimechat.ConversationListActivity
com.google.android.apps.plus    com.google.android.apps.plus.phone.ConversationListActivity

Users

activity                        class
com.google.android.apps.plus    com.google.android.apps.circles.realtimechat.ConversationListActivity

I need it to select this missing activity/class which is 

activity                        class
com.google.android.apps.plus    com.google.android.apps.plus.phone.ConversationListActivity<br>

I have tried this and it gives me all the activity/classes. Now if i can just remove the ones already in users thats what i need.

SELECT
  rc.activity, rc.class
FROM results_class rc
  INNER JOIN user b
    ON b.activity = rc.activity

Upvotes: 1

Views: 102

Answers (2)

Sumeet Sharma
Sumeet Sharma

Reputation: 2583

Try this .. The below query emulates the MINUS operation ie matches all in class a which are not in b.

SELECT a.activity,a.class FROM results_class a 
left join user b on (a.activity = b.activity and a.class = b.class) 
where b.class is null

Working fiddle

AFTER EDIT considering the missed information in the question "If there is a activity in results_class and not in users its bringing it back too. I only need matching activities to bring non matching classes"

select a1.activity,a1.class from (select a.activity,a.class FROM results_class a 
left join user b on (a.activity = b.activity and a.class = b.class) 
where b.class is null) a1 inner join user b1 on a1.activity = b1.activity

Upvotes: 1

John Ruddell
John Ruddell

Reputation: 25842

Try using a subquery with exists

SELECT  rc.activity, rc.class
FROM results_class rc
JOIN users c on c.activity = rc.activity
WHERE NOT EXISTS
(   SELECT 1
    FROM user b 
    WHERE b.class = rc.class
)

So basically you get all of the results_class activities when joined to users.. then filter out the classes that are in results_class but not in users.

WORKING FIDDLE

Upvotes: 0

Related Questions