SQL query to only select row if specific condition is met

Question

Consider the following SQL query:

SELECT
friends.name as friendname,
friends.surname as friendsurname,
friends.number as friendnumber,
friends.gender as friendgender, 
clients.name as clientname,
clients.surname as clientsurname
FROM friends
INNER JOIN clients
ON friends.clientid = clients.id
WHERE datetime(friends.creationdate, 'localtime') >= datetime('SOME_TEST_TIME') AND datetime(friends.creationdate, 'localtime') <= datetime('SOME_TEST_TIME')

Both the friends and clients tables have a number column that I want to compare in this query: IF friends.number is already in clients' table (clients.number column) then don't select that particular row anymore. How is it possible to accomplish this in one query?

Example:

Table clients:
--------------------------
id  name  surname number

1   john  smith   55555
2   sam   wesker  12345
3   adam  Nye     48745
--------------------------

Table friends:

----------------------------------
id  name  surname number clientid

1   abcd  qwert   88888  2
2   dddd  asdfg   48745  2
3   ffff  zxcvb   77777  3
----------------------------------

The query should omit the second row in friends because its number is present in clients' table

I note that you're missing a comma at friendgender. And you have one more than necessary at clientsurname — Strawberry
– Strawberry, Commented Apr 29, 2015 at 14:38
I'm not familiar with the DATETIME function. Can you tell us more about that? — Strawberry
– Strawberry, Commented Apr 29, 2015 at 14:46
@Strawberry Converts the first parameter to DATETIME format that SQL engine (sqlite in my case but same goes for MySQL) can compare to another DATETIME for example. In this particular query it is meant to select only those rows that are between the two "SOME_TEST_TIME" periods. The format of that string, in case of SQLite would be: yyyy-MM-dd HH:mm:ss — astralmaster
– astralmaster, Commented Apr 29, 2015 at 14:50

Jules · Accepted Answer · 2015-04-29 14:32:28Z

2

Add :

AND friends.number NOT IN (SELECT clients.number FROM clients)

answered Apr 29, 2015 at 14:32

Jules

2951 silver badge10 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

astralmaster Over a year ago

Yep. Quite elegant, too, in my opinion

Collectives™ on Stack Overflow

SQL query to only select row if specific condition is met

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related