CODEWITHSUNDEEP
sqljoin
user2917559
user2917559

Reputation: 163

SQL Join two tables with an excluding condition?

I have a SQL question which I am trying to solve. Below is the question:

Given the following two tables write a SQL query that returns the top 10000 scores excluding uuids that have any what=3 events in the last 30 days.

TABLE scores (
  uuid INT,
  score FLOAT
)
TABLE events (
  when date,
  uuid INT, 
  what INT
)

I tried to solve it in this way by dividing the question into two statements and then joining it,

For the statement: SQL query that returns the top 10000 scores, I got,

SELECT TOP 10000 score FROM scores;

For the statement:uuids that have any what=3 events in the last 30 days.

SELECT uuid FROM events WHERE what=3 AND when>2/14/2015;

By joining the two statements based on the excluding condition, i got,

SELECT TOP 10000 score
WHERE what!=3 AND when>2/14/2015
FROM scores
INNER JOIN events
ON score.uuid=events.uuid;

But I am not sure if this correct, and I feel like I am missing something here like a specific syntax for "excluding", and also I am not sure if I performed the Join operation in the right way. Any help would be appreciated.

Upvotes: 0

Views: 96

Answers (1)

avk
avk

Reputation: 871

Try this

select top 10000 score
from scores
where not exists(
     select 1 
     from events
     where scores.uuid = events.uuid
     and events.when > 20150214 
     and what=3 
    )

Upvotes: 1

Related Questions