?
Solved

distinct multi inner join with group by and having count

Posted on 2003-02-24
19
Medium Priority
?
596 Views
Last Modified: 2008-02-07
Is it possible to combine these two queries?

select a.rpt_key, a.rh_dt, a.emp_key,
a.rh_appv_status_key, b.aps_name, a.rh_payment_status_key, c.pay_name,
a.rh_amt_total, a.rh_amt_due_emp, a.rh_amt_approv, a.rh_amt_paid, a.rh_source, a.rh_desc, a.rh_comment
from qx_approv_status b, qx_pay_status c, qx_report_history a
where a.rh_appv_status_key = b.aps_pkey
and a.rh_payment_status_key = c.pay_pkey

with

select distinct(rpt_key) as 'duplicate rpt_key records found'
from qx_report_history group by rpt_key having count(rpt_key)>1 order by 1 desc

?

This is what I have so far but I get an error:
Column '<insert any column name from line 1 above>' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

select distinct(a.rpt_key), a.rpt_key, a.rh_dt, a.emp_key,
a.rh_appv_status_key, b.aps_name, a.rh_payment_status_key, c.pay_name,
a.rh_amt_total, a.rh_amt_due_emp, a.rh_amt_approv, a.rh_amt_paid, a.rh_source, a.rh_desc, a.rh_comment
from  qx_report_history a
inner join qx_approv_status b on a.rh_appv_status_key = b.aps_pkey
inner join qx_pay_status c on a.rh_payment_status_key = c.pay_pkey
group by a.rpt_key
having count(a.rpt_key)>1
order by 1 desc

Thanks.
0
Comment
Question by:jockovonred
[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
  • 9
  • 4
  • 3
  • +2
19 Comments
 
LVL 1

Expert Comment

by:Divaqs
ID: 8012156
Hi jockovonred,

Have you tried a sub-query?

Like this:

select
  a.rpt_key,
  a.rh_dt,
  a.emp_key,
  a.rh_appv_status_key,
  b.aps_name,
  a.rh_payment_status_key,
  c.pay_name,
  a.rh_amt_total,
  a.rh_amt_due_emp,
  a.rh_amt_approv,
  a.rh_amt_paid,
  a.rh_source,
  a.rh_desc,
  a.rh_comment,
  (select count(rpt_key)
   as NumCounted
   from qx_report_history
   group by rpt_key
   having count(rpt_key)>1
   order by 1 desc)
   As 'number of duplicate rpt_key records found'
  from qx_approv_status b, qx_pay_status c, qx_report_history a
where a.rh_appv_status_key = b.aps_pkey
and a.rh_payment_status_key = c.pay_pkey
and a.rpt_key = 1112
0
 
LVL 32

Expert Comment

by:Brendt Hess
ID: 8012171
See if this does what you need:

select a.rpt_key, a.rpt_key, a.rh_dt, a.emp_key,
a.rh_appv_status_key, b.aps_name, a.rh_payment_status_key, c.pay_name,
a.rh_amt_total, a.rh_amt_due_emp, a.rh_amt_approv, a.rh_amt_paid, a.rh_source, a.rh_desc, a.rh_comment
from  qx_report_history a
inner join qx_approv_status b on a.rh_appv_status_key = b.aps_pkey
inner join qx_pay_status c on a.rh_payment_status_key = c.pay_pkey
where a.rh_appv_status_key = b.aps_pkey
and a.rh_payment_status_key = c.pay_pkey
and a.rpt_key = 1112
AND a.rpt_key in (Select rpt_key from qx_report_history group by rpt_key HAVING count(rpt_key) > 1)

0
 

Accepted Solution

by:
atplack earned 300 total points
ID: 8012204
Hello jockovonred,

Here is what you asked, but I do not believe it is what you need.  I have changed the query from DISTINCT to SUM

select d.num_rpt_keys,
       a.rpt_key,
       a.rh_dt,
       a.emp_key,
       a.rh_appv_status_key,
       b.aps_name,
       a.rh_payment_status_key,
       c.pay_name,
       a.rh_amt_total,
       a.rh_amt_due_emp,
       a.rh_amt_approv,
       a.rh_amt_paid,
       a.rh_source,
       a.rh_desc,
       a.rh_comment
from qx_report_history a
       INNER JOIN qx_approv_status b ON a.rh_appv_status_key = b.aps_pkey
       INNER JOIN qx_pay_status c ON a.rh_payment_status_key = c.pay_pkey
       INNER JOIN (select rpt_key, COUNT(rpt_key) AS num_rpt_keys from qx_report_history group by rpt_key having count(rpt_key)>1) AS d ON d.rpt_key = a.rpt_key

I am not sure if this is your desired outcome or not.  It will find any rpt_key that is duplicate and display the results of the duplicates tied to the other tables.  If there are 5 rpt_key's in the table it will show all 5.

0
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 

Author Comment

by:jockovonred
ID: 8012244
the statement should not include
'and a.rpt_key = 1112'
that must be taken out to find all instances of duplicates.  the query is to find all the duplicates then only show information for those duplicated items.

the first query if very specific and combining the two is to find duplicates, not specifically based upon the rpt_key.


thus, it should be combining:
select a.rpt_key, a.rh_dt, a.emp_key,
a.rh_appv_status_key, b.aps_name, a.rh_payment_status_key, c.pay_name,
a.rh_amt_total, a.rh_amt_due_emp, a.rh_amt_approv, a.rh_amt_paid, a.rh_source, a.rh_desc, a.rh_comment
from qx_approv_status b, qx_pay_status c, qx_report_history a
where a.rh_appv_status_key = b.aps_pkey
and a.rh_payment_status_key = c.pay_pkey

with

select distinct(rpt_key) as 'number of duplicate rpt_key records found'
from qx_report_history group by rpt_key having count(rpt_key)>1 order by 1 desc

thanks.
0
 

Author Comment

by:jockovonred
ID: 8012258
Divags:

the query returns:
The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP is also specified.
0
 
LVL 1

Expert Comment

by:Divaqs
ID: 8012281
Hi jockovonred,

Try TOP 100 PERCENT
0
 

Author Comment

by:jockovonred
ID: 8012309
Divags:

adding TOP 100 PERCENT to bottom of query, returns same error.
0
 
LVL 32

Expert Comment

by:Brendt Hess
ID: 8012358
select a.rpt_key, a.rpt_key, a.rh_dt, a.emp_key,
a.rh_appv_status_key, b.aps_name, a.rh_payment_status_key, c.pay_name,
a.rh_amt_total, a.rh_amt_due_emp, a.rh_amt_approv, a.rh_amt_paid, a.rh_source, a.rh_desc, a.rh_comment
from  qx_report_history a
inner join qx_approv_status b on a.rh_appv_status_key = b.aps_pkey
inner join qx_pay_status c on a.rh_payment_status_key = c.pay_pkey
where a.rh_appv_status_key = b.aps_pkey
and a.rh_payment_status_key = c.pay_pkey
AND a.rpt_key in (Select rpt_key from qx_report_history group by rpt_key HAVING count(rpt_key) > 1)
0
 

Author Comment

by:jockovonred
ID: 8012365
Divags:

adding TOP 100 PERCENT to bottom of query, returns same error.
0
 

Author Comment

by:jockovonred
ID: 8012378
bhess1:

same results as orignal query (minus the rpt_key = 1112)
0
 

Author Comment

by:jockovonred
ID: 8012397
bhess1:

same results as orignal query (minus the rpt_key = 1112)
0
 
LVL 1

Expert Comment

by:Divaqs
ID: 8012402
Hi jockovonred,

I just successfully parsed this SQL:

select
 a.rpt_key,
 a.rh_dt,
 a.emp_key,
 a.rh_appv_status_key,
 b.aps_name,
 a.rh_payment_status_key,
 c.pay_name,
 a.rh_amt_total,
 a.rh_amt_due_emp,
 a.rh_amt_approv,
 a.rh_amt_paid,
 a.rh_source,
 a.rh_desc,
 a.rh_comment,
 (select top 100 percent count(rpt_key)
  as NumCounted
  from qx_report_history
  group by rpt_key
  having count(rpt_key)>1
  order by 1 desc)
  As 'number of duplicate rpt_key records found'
 from qx_approv_status b, qx_pay_status c, qx_report_history a
where a.rh_appv_status_key = b.aps_pkey
and a.rh_payment_status_key = c.pay_pkey
0
 

Author Comment

by:jockovonred
ID: 8012414
Divags:

using SQL query analyzer i receive:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
0
 

Author Comment

by:jockovonred
ID: 8012449
the count is not important in the return, what is important is finding duplicates then returning those with the other info.

thus the statement should include distinct().

please note i removed the rpt_key = 1112 from the original question.
0
 
LVL 1

Expert Comment

by:Divaqs
ID: 8012491
Hi jockovonred,

I realized after I posted that this would likely be an issue, since the "having count(rpt_key)>1" in the sub-query would cause problems.

So I moved it to the general select's conditions

SELECT
 a.rpt_key,
 a.rh_dt,
 a.emp_key,
 a.rh_appv_status_key,
 b.aps_name,
 a.rh_payment_status_key,
 c.pay_name,
 a.rh_amt_total,
 a.rh_amt_due_emp,
 a.rh_amt_approv,
 a.rh_amt_paid,
 a.rh_source,
 a.rh_desc,
 a.rh_comment,
 (select top 100 percent count(rpt_key)
  as NumCounted
  from qx_report_history
  group by rpt_key
  order by 1 desc)
  As Duplicates
FROM qx_approv_status b, qx_pay_status c, qx_report_history a
WHERE
  a.rh_appv_status_key = b.aps_pkey
  and a.rh_payment_status_key = c.pay_pkey
  and Duplicates > 1

I am not sure about the joins working right, without having some test data to run this against. I would highly recommend testing those. But the idea of using a sub-query is probably what you are needing to combine the two queries. The other option is to use an inline user-defined function.
0
 

Author Comment

by:jockovonred
ID: 8012511
atplack:

this query is useful as it shows the number of duplicates.  i wonder if it can be tweaked to only show the duplicate rpt_key and it's associated info?
0
 

Expert Comment

by:atplack
ID: 8014144
It can but I need more information.

If I have a rpt_key, how do I know which is the original and which is duplicate?  Is it ordered by a date?

Second, are you asking for the original rpt_key or you wish to see the duplicate rpt_key 's

ie  If you have 3 rpt_key's, do you want to see the first one only or the last 2?

Both parts are important to your answer.
0
 
LVL 3

Expert Comment

by:hakyemez
ID: 8015046
Use This Query:

select      a.rpt_key,
     a.rh_dt,
     a.emp_key,
     a.rh_appv_status_key,
     b.aps_name,
     a.rh_payment_status_key,
     c.pay_name,
     a.rh_amt_total,
     a.rh_amt_due_emp,
     a.rh_amt_approv,
     a.rh_amt_paid,
     a.rh_source,
     a.rh_desc,
     a.rh_comment
from      qx_approv_status as b,
     qx_pay_status as c,
     qx_report_history as a,
(select x.rpt_key from qx_report_history as x group by x.rpt_key having count(*)>1) as z
where      a.rh_appv_status_key = b.aps_pkey
     and a.rh_payment_status_key = c.pay_pkey
     and z.rpt_key = a.rpt_key
0
 

Expert Comment

by:atplack
ID: 8016113
It can but I need more information.

If I have a rpt_key, how do I know which is the original and which is duplicate?  Is it ordered by a date?

Second, are you asking for the original rpt_key or you wish to see the duplicate rpt_key 's

ie  If you have 3 rpt_key's, do you want to see the first one only or the last 2?

Both parts are important to your answer.
0

Featured Post

Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

752 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