How to filter data on one table based on reference data on another table?

Question

I have two tables account_agent and bulk_index. The data on each table is as follows

account_agent

name         code         account         remark         scheme
avi          10           151362          first          as
babi         11           123478          new            as
avi          10           151721          new            as
avi          10           151765          new            as
sam          sas/01       925458          tin            sas

bulk_index

agentcode         account          customer_name
10                151362           Alok
22                265721           Rohan
10                151721           Akash

using these tables I want result on account_agent table as follows

account_agent

name         code         account         remark
avi          10           151362          first
avi          10           151721          new

I have tried the query

select * from account_agent where code = '10' and account = (select account from bulk_index where code = '10')

Solution 1

This query worked for me

SELECT A.name, A.code, A.Account, A.remark, A.scheme
FROM account_agent A
INNER JOIN bulk_index B ON A.account = B.account 
WHERE A.scheme = 'as' AND A.code = '10'

Solution 2

Assuming your account column is unique, you could just do a simple join and a where clause to get this:

SELECT A.name, A.code, A.Account, A.remark
FROM account_agent A
INNER JOIN bulk_index B ON A.account = B.account 
WHERE a.scheme like '%as%' and a.code = '10' 
--remove the preceeding or succeeding % in like as needed

If you are only interested in values where scheme column is equal to 'as' and code is equal to 10, then you could just do this:

SELECT A.name, A.code, A.Account, A.remark
FROM account_agent A
INNER JOIN bulk_index B ON A.account = B.account 
WHERE a.scheme = 'as' and a.code = '10'

Here is a link to W3Schools page which explains the use of like operator in sql.

SQL LIKE Operator