1

I have a table with data as below:

FILTER Table

**id    filter**    
4638    Aabe    
4639    Aaby    
4640    Aadl    
4641    Aaga    
4642    Aake

SURNAMES Table

**surnames**    
Aaberge    
Aabehut    
Aabyuti    
Aabytis    
Aadlit    
Aagastha    
Aaker

I am trying to write an inner query to retrieve a count of surnames that match filters from the filter table

Below is what i have based on separate select statements

  1. select * from filter
  2. select count(*) from surnames where last_name like 'Aabe%' or last_name like 'Aaby%' group by last_name;

How can I write an inner query for this condition since the record count is large?

Aldwoni
  • 1,168
  • 10
  • 24
Stackoverflow User
  • 161
  • 1
  • 4
  • 10
  • `...JOIN on SURNAMES.surnames LIKE CONCAT(FILTER.filter,'%')` – Mihai Oct 15 '15 at 18:12
  • select * from surnames, filters where filters.id > 4215 JOIN on SURNAMES.last_name LIKE CONCAT(filters.filter,'%') ?? Correct me if I am wrong. Thank you very much!! – Stackoverflow User Oct 15 '15 at 18:22
  • `select *,COUNT(*) from surnames join filters on SURNAMES.last_name LIKE CONCAT(filters.filter,'%') where filters.id > 4215 group by last_name` – Mihai Oct 15 '15 at 18:23
  • Works. Thank you very much. I dont know how I can mark this answer as correct. – Stackoverflow User Oct 15 '15 at 20:29
  • @Mihai can you please add your comment as an answer so that OP can mark it as accepted and bring closure to this question? – zedfoxus Oct 15 '15 at 21:37

1 Answers1

0
select *,
COUNT(*) from surnames join filters 
on SURNAMES.last_name LIKE CONCAT(filters.filter,'%') 
where filters.id > 4215 
group by last_name
Mihai
  • 26,325
  • 7
  • 66
  • 81