jockovonred
asked on
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.
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.
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)
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)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
'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.
ASKER
Divags:
the query returns:
The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP is also specified.
the query returns:
The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP is also specified.
Hi jockovonred,
Try TOP 100 PERCENT
Try TOP 100 PERCENT
ASKER
Divags:
adding TOP 100 PERCENT to bottom of query, returns same error.
adding TOP 100 PERCENT to bottom of query, returns same error.
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)
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)
ASKER
Divags:
adding TOP 100 PERCENT to bottom of query, returns same error.
adding TOP 100 PERCENT to bottom of query, returns same error.
ASKER
bhess1:
same results as orignal query (minus the rpt_key = 1112)
same results as orignal query (minus the rpt_key = 1112)
ASKER
bhess1:
same results as orignal query (minus the rpt_key = 1112)
same results as orignal query (minus the rpt_key = 1112)
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
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
ASKER
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.
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.
ASKER
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.
thus the statement should include distinct().
please note i removed the rpt_key = 1112 from the original question.
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.
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.
ASKER
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?
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?
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.
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.
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
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
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.
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.
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