CODEWITHSUNDEEP
mysqlsql
user10249086
user10249086

Reputation:

mysql compare 2 substring columns

I'm trying to find how many simular domains there are in a table according to the emails table.

Sample data:

domains              emails
_______              ______ 
gmail.nl             [email protected]
gmail.com            [email protected]
gmail.uk

I tried using this query:

SELECT (SELECT COUNT(*) FROM domains
    WHERE 
    right(domain, INSTR(domain, '.')-1) 
    LIKE 
    CONCAT('%', right(email, length(email)-INSTR(email, '@')), '%')) as "total",
    right(email, length(Email)-INSTR(Email, '@')) FROM emails GROUP BY email

Here I'm comparing a part of a string from one table with another substring from another table.
Take a look at this DBFIDDLE: https://www.db-fiddle.com/f/kdf9iwaEPPz6kMpHqWhZkh/0

I'm getting 0 as result for each row, but it should be 3 for gmail.
Does anyone know how to do this?

Upvotes: 1

Views: 1046

Answers (1)

user10249086
user10249086

Reputation:

I think Ill fixed it:

This my query now:

SELECT (SELECT COUNT(*) FROM domains
    WHERE 
    emails.email
    LIKE 
    CONCAT('%', LEFT(domain, INSTR(domain, '.')-1), '%') 
    ) as "total",
    right(emails.email, length(emails.email)-INSTR(emails.email, '@')) FROM emails GROUP BY emails.email

I got the desired result!
I switched the columns and removed the substring from email.

Upvotes: 1

Related Questions