How to use JOIN in WHERE clause in SQL statement?

Question

Below are 2 collections - Merchants and Tokens.
Tokens is a table that holds tokens for a merchant to use.
Each merchant can have multiple tokens.

Table: Merchants
Columns:

Id (nvarchar, not null),
Name (nvarchar, not null),
PartnerId (nvarchar, not null),
Group (nvarchar, not null),
JoinDate (nvarchar, not null)

Table: Tokens
Columns:

Id (nvarchar, not null),
InsertDate (DateTime, not null),
MerchantId (nvarchar, not null),
TokenType (int, not null),

I am trying to write an SQL query that will fetch merchants that have inactive tokens
that were created after Aug 30, 2021 for merchants that joined after Jan 1, 2020.
Required output: merchant name, merchant join date.
Rows with duplicate data should not be displayed.

I tried to do the following:

SELECT Merchants.Name, Merchants.JoinDate
FROM Merchants
WHERE Tokens.TokenType=1 AND Tokens.InsertDate > 8/30/2021 AND Merchants.JoinDate > 1/1/2020
LIMIT 1;

However, this doesnt seem to work.
I am new to SQL and I think I should use JOIN in the WHERE clause,
but I can`t seem to make it work.
Can someone please assist?

You can't do a JOIN in the where clause, but you can do one before. That will bring the Tokens table into scope, which will let you filter in its columns in your WHERE clause. — darkmnemic
– darkmnemic, Commented Nov 22, 2021 at 22:23
Note that LIMIT 1 doesn't delete duplicate data, it shows you only the first record. To avoid duplicates you need to add 'distinct' in select statement — Gius
– Gius, Commented Nov 22, 2021 at 22:42

Gius · Accepted Answer · 2021-11-22 22:37:25Z

0

This should be good.

SELECT DISTINCT m.Name, m.JoinDate
FROM Merchants m JOIN Tokens t ON m.Id = t.MerchantId
WHERE t.InsertDate > '8/30/2021' AND m.JoinDate > '1/1/2020' AND t.TokenType = 1

Joins are inserted in FROM statement, while in WHERE statement you need to add attribute's condictions (selection operator of relational algebra)

answered Nov 22, 2021 at 22:37

Gius

14010 bronze badges

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

Collectives™ on Stack Overflow

How to use JOIN in WHERE clause in SQL statement?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related