Solved

help with tricky query.

Posted on 2015-02-11
3
50 Views
Last Modified: 2015-02-12
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
Joseph  Dreadson
Terry   James




Table_2
FNAME   LNAME
Bob     Candoiti
Mary    Semper
Mary    Semper
Joseph  Dreadson
Joseph  Dreadson
Joseph  Dreadson
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
 :

Joseph Deadson
Terry  James
0
Comment
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
  • Learn & ask questions
  • 2
3 Comments
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 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
;

Open in new window


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

Open in new window

0
 
LVL 2

Author Closing Comment

by:brgdotnet
ID: 40607335
Awesome!
0
 
LVL 48

Expert Comment

by:PortletPaul
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question