Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# help with tricky query.

Posted on 2015-02-11
Medium Priority
53 Views
I need to compare Table_1 and Table_2.

Table_1 and Table_2 both have duplicate records. Table_2 has more duplicate records in it than table_1.
I need a list of all of the names in table_2 that are also in table_1. But I want the names of those in Table_2
that have more duplicate names in table_2. So for example, below both tables have Joseph Dreadson in them.

Table_2 has more records in it than Table_1.

I need a list of all of the names in Table_2 that have more records than their matching names in
Table_1.

So for these two tables :

Table_1
FNAME   LNAME
Bob     Candoiti
Mary    Semper
Mary    Semper
Mary    Semper
Terry   James

Table_2
FNAME   LNAME
Bob     Candoiti
Mary    Semper
Mary    Semper
Terry   James
Terry   James

So the desired query would return these two records, because these people occur more frequently in table_2 than table_1
:

Terry  James
0
Question by:brgdotnet
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 2

LVL 49

Accepted Solution

PortletPaul earned 2000 total points
ID: 40605009
``````SELECT
coalesce(t1.fname,t2.fname) as fname
, coalesce(t1.lname,t2.lname) as lname
, t1.count_t1
, t2.count_t2
FROM (
SELECT
fname
, lname
, COUNT(*) AS COUNT_t1
FROM table_1
GROUP BY
fname
, lname
) T1
FULL OUTER JOIN (
SELECT
fname
, lname
, COUNT(*) AS COUNT_t2
FROM table_2
GROUP BY
fname
, lname
) T2 ON T1.fname = T2.fname
AND T1.lname = T2.lname
WHERE T2.count_t2 > T1.count_t1
;
``````

``````|  FNAME |    LNAME | COUNT_T1 | COUNT_T2 |
|--------|----------|----------|----------|
| Joseph | Dreadson |        1 |        3 |
|  Terry |    James |        1 |        2 |
``````
0

LVL 2

Author Closing Comment

ID: 40607335
Awesome!
0

LVL 49

Expert Comment

ID: 40607386
WHERE T2.count_t2 > T1.count_t1
OR T1.count_t1 IS NULL
OR T2.count_t1 IS NULL

it may need to cater for NULLs which I forgot
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
###### Suggested Courses
Course of the Month6 days, 4 hours left to enroll