473,327 Members | 1,976 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

Need help with join - it's returning duplicate rows

I have re-written this several times (changing the joins to sub selects and various other tricks), and I am sure it's something very, very simple, but I am just missing it. Any help is most appreciated.

I am trying to query the ReportServer database that is installed on Reporting Services. I want to grab all reports that have subscriptions, and grab when the subscription last ran as well as it's status (ex: "Mail sent to userA, userB, etc.")

Here is my query:
select distinct
C.Name,
S.LastStatus,
Sched.LastRunTime

from Schedule Sched
join ReportSchedule RS on Sched.ScheduleID = RS.ScheduleID
join Catalog C on RS.ReportID = C.ItemID
join Subscriptions S on C.ItemID = S.Report_OID

Order by C.Name


The result I am getting is as follows (sorry about the wrapping):

Weekly Status Mail sent to smitha 2009-04-13 08:00:07.943
Daily Status Mail sent to Budgets 2009-04-13 08:00:07.520
Daily Status Mail sent to Budgets 2009-04-13 08:00:08.100
Daily Status Mail sent to Budgets 2009-04-13 08:00:08.147
Daily Status Mail sent to Admins 2009-04-13 08:00:07.520
Daily Status Mail sent to Admins 2009-04-13 08:00:07.520
Daily Status Mail sent to Admins 2009-04-13 08:00:07.520
Daily Status The e-mail address of one or more recipients is not valid. 2009-04-13 08:00:07.520
Daily Status The e-mail address of one or more recipients is not valid. 2009-04-13 08:00:08.100
Daily Status The e-mail address of one or more recipients is not valid. 2009-04-13 08:00:08.147

And here's what I would like to get:
Weekly Status Mail sent to smitha 2009-04-13 08:00:07.943
Daily Status Mail sent to Budgets 2009-04-13 08:00:07.520
Daily Status Mail sent to Admins; 2009-04-13 08:00:07.520
Daily Status The e-mail address of one or more recipients is not valid. 2009-04-13 08:00:08.147
Apr 14 '09 #1
3 6845
ck9663
2,878 Expert 2GB
I don't have that database installed in my server, could you post some sample data from the tables?


--- CK
Apr 15 '09 #2
select
C.Name,
S.LastStatus,
max(Sched.LastRunTime)

from Schedule Sched
join ReportSchedule RS on Sched.ScheduleID = RS.ScheduleID
join Catalog C on RS.ReportID = C.ItemID
join Subscriptions S on C.ItemID = S.Report_OID
group by C.Name,
S.LastStatus
Order by C.Name
Apr 16 '09 #3
Thank you, kunalsmehta! It was the group by and max that I was missing.

Thanks again!
Jennifer
Apr 17 '09 #4

Sign in to post your reply or Sign up for a free account.

Similar topics

4
by: Russell | last post by:
I'm using MySQL 4.1.1 I've inherited a database which has some (almost) duplicate rows. The databse is like this. userID userPosition userDepartment
3
by: OC | last post by:
I have a datatable for which I have bound a DataView. I have three rows in the table, with rowid = '12310104', '12314402', and '12315208'. When I do a dvHdr.RowFilter = '12314402', it returns a...
2
by: g_chime | last post by:
I'm sorry, I had a couple of typos in my original post. Here is the corrected version: How can I make MySQL reject duplicate rows when inserting? The table is quite large: 100+ columns and...
1
by: Jeff Silverman | last post by:
I have a PHP program that almost works. I'm running it from the command line and simulating a form using a GET method. That part is working, but I get spurious records with all of the fields...
6
by: Bob Stearns | last post by:
I am getting duplicate rows back from a select distinct statement of the form: SELECT DISTINCT 'jhough', '000111', t0.bhid FROM (SELECT lots of good stuff) t0 LEFT OUTER JOIN another_table ...
7
by: Jon Maz | last post by:
Hi, I have a MySql problem I hope someone can help me with. I'm trying to run an update on a linking table, the update is running into a Primary Key constraint violation, and in my workaround...
40
by: rdemyan via AccessMonster.com | last post by:
I have two databases, db1 and db2, with the same table, TableA. I want to select the records from TableA in db1 that have a LAST_UPDATE SomeDate. Then I want to get the identical records in TableA...
1
by: Phil Latio | last post by:
I have a number of spreadsheets, each with between 1000-6000 rows (each row is a property) and they all need to be combined into a single database. Each spreadsheet contains slightly different...
4
by: ravir81 | last post by:
Hi, I am currently working on excel validation using Perl. I am new to Excel validation but not for Perl. I have a question regarding one of the validation. Could anyone please tell me how to get...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.