Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 601
  • Last Modified:

distinct multi inner join with group by and having count

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
jockovonred
Asked:
jockovonred
  • 9
  • 4
  • 3
  • +2
1 Solution
 
DivaqsCommented:
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
 
Brendt HessSenior DBACommented:
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
 
atplackCommented:
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
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
jockovonredAuthor Commented:
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
 
jockovonredAuthor Commented:
Divags:

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

Try TOP 100 PERCENT
0
 
jockovonredAuthor Commented:
Divags:

adding TOP 100 PERCENT to bottom of query, returns same error.
0
 
Brendt HessSenior DBACommented:
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
 
jockovonredAuthor Commented:
Divags:

adding TOP 100 PERCENT to bottom of query, returns same error.
0
 
jockovonredAuthor Commented:
bhess1:

same results as orignal query (minus the rpt_key = 1112)
0
 
jockovonredAuthor Commented:
bhess1:

same results as orignal query (minus the rpt_key = 1112)
0
 
DivaqsCommented:
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
 
jockovonredAuthor Commented:
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
 
jockovonredAuthor Commented:
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
 
DivaqsCommented:
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
 
jockovonredAuthor Commented:
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
 
atplackCommented:
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
 
hakyemezCommented:
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
 
atplackCommented:
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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

  • 9
  • 4
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now