Query Based On Common Criteria From Two Tables And Sort Based On Number Of Occurrences

I have the following tables that I need to run query against each. userA id name title ---------- ---------- -------- 1 john engineer 1

Solution 1:

Try this:

Create a VIEW that combines users from both tables.

CREATEVIEW userA_B asselect*, 'A'as source from userA
  unionallselect*, 'B'as source from userB;

Data in this view

select * from userA_B;

id          name        title       source    
----------  ----------  ----------  ----------
1           john        engineer    A1           john        engineer    A2           mike        designer    A3           laura       manager     A4           dave        engineer    A1           john        engineer    B3           laura       manager     B3           laura       manager     B3           laura       manager     B5           peter       sales       B4           dave        engineer    B

Create a VIEW that shows you only those users who appear in both tables.

CREATEVIEW user_in_both_A_B asselect id, name, title, count(*) as total_appearance
  from userA_B
  groupby id, name, title
  havingcount(distinct source) =2;

Data in this view

select*from user_in_both_A_B;

id          name        title       total_appearance
----------  ----------  ----------  ----------------1           john        engineer    33           laura       manager     44           dave        engineer    2

Create a VIEW that shows you the title that appears the most.

CREATE VIEW title_appearing_most asselect title, max(total_appearance) as max_total_appearance
  from user_in_both_A_B
  groupby title

Data in this view

select*from title_appearing_most;

title       max_total_appearance
----------  --------------------
engineer    3                   
manager     4

Now, get only those records from user_in_both_A_B view that have title and # of appearances matching in title_appearing_most.

select ab.*
from user_in_both_A_B ab
inner join title_appearing_most m
    on ab.title = m.title
    and ab.total_appearance = m.max_total_appearance;

Final Result

id          name        title       total_appearance
----------  ----------  ----------  ----------------
1           john        engineer    3               
3           laura       manager     4               

Views will help you store a query that can be executed on demand, and with a shorter name. Sub-queries inside sub-queries can be visually avoided, making reading simpler.