Need Help with a query returning too much info

[mattmontalto]

I once found a genius on this forum who helped me with a complicated query I needed.... Now I am looking for another.... I have created a DB that has 3 tables, main_tbl, victim_tbl & suspect_tbl.

I then have a query that is selecting all fields of all tables, mainly so that every field is searchable via the web application I am setting up for the users.

The problem is with the results returned from this large query... For each record in the main_tbl, there is one case # (called scsv_no) and it is returned multiple times... one time for each victim and suspect in the child tables... So if there are two victims and two suspects that are related to one scsv_no, then I see that scsv_no returned four times in the main query's results.

I am trying to explore fixing this using the "GROUP BY" function, but I need a little guidance... any help would be greatly appreciated... Pasted below is the actual query. Thanks in advance.

SELECT
dbo.main_tbl.id,
dbo.main_tbl.scsv_no,
dbo.main_tbl.datereceived,
dbo.main_tbl.CaseOrigin,
dbo.main_tbl.CaseOrigin_Other,
dbo.main_tbl.OrigADA,
dbo.main_tbl.OrigADA_active,
dbo.main_tbl.ADA1,
dbo.main_tbl.ADA1_active,
dbo.main_tbl.ADA2,
dbo.main_tbl.ADA2_active,
dbo.main_tbl.ChldAbsRef,
dbo.main_tbl.Docket,
dbo.main_tbl.fileno,
dbo.main_tbl.IndSci,
dbo.main_tbl.JD,
dbo.main_tbl.IDV,
dbo.main_tbl.Charge,
dbo.main_tbl.AbuseType1,
dbo.main_tbl.AbuseType1_other,
dbo.main_tbl.AbuseType2,
dbo.main_tbl.AbuseType2_other,
dbo.main_tbl.dviolence,
dbo.main_tbl.DateofOccurStart,
dbo.main_tbl.DateofOccurEnd,
dbo.main_tbl.LocAdd1,
dbo.main_tbl.LocAdd2,
dbo.main_tbl.City,
dbo.main_tbl.[State],
dbo.main_tbl.Zip,
dbo.main_tbl.CaseWorker,
dbo.main_tbl.VicAdv,
dbo.main_tbl.VicAdv_active,
dbo.main_tbl.Detective,
dbo.main_tbl.Det_Active,
dbo.main_tbl.Disposition,
dbo.main_tbl.Sentence,
dbo.main_tbl.Notes,
dbo.main_tbl.RelatedToCaseNo,
dbo.main_tbl.CaseOpen,
dbo.main_tbl.CaseClosed,
dbo.main_tbl.DateClosed,
dbo.victim_tbl.id AS id1,
dbo.victim_tbl.main_tbl_id,
dbo.victim_tbl.[Last],
dbo.victim_tbl.[First],
dbo.victim_tbl.sex,
dbo.victim_tbl.dob,
dbo.victim_tbl.age,
dbo.victim_tbl.race,
dbo.victim_tbl.race_other,
dbo.suspect_tbl.id AS id2,
dbo.suspect_tbl.main_tbl_id AS main_tbl_id1,
dbo.suspect_tbl.[Last] AS Last1,
dbo.suspect_tbl.[First] AS First1,
dbo.suspect_tbl.sex AS sex1,
dbo.suspect_tbl.dob AS dob1,
dbo.suspect_tbl.age AS age1,
dbo.suspect_tbl.race AS race1,
dbo.suspect_tbl.race_other AS race_other1,
dbo.suspect_tbl.RelationtoVictim,
dbo.suspect_tbl.RelationtoVictim_other
FROM dbo.main_tbl
LEFT OUTER JOIN dbo.victim_tbl ON dbo.main_tbl.id = dbo.victim_tbl.main_tbl_id
LEFT OUTER JOIN dbo.suspect_tbl ON dbo.main_tbl.id = dbo.suspect_tbl.main_tbl_id
ORDER BY dbo.main_tbl.scsv_no DESC, dbo.main_tbl.id DESC

 

[gmmastros]

I think we need a little more clarification first.

If a case (scsv_no) has 1 suspect and 2 victims, what should the output look like? If there is 1 suspect and 1 victim. 4 suspects and 2 victims?

[tt]
scsv_no Suspect Victim
1 Bill Jane
1 NULL Jill
2 Bob Mary
3 Arthur Tina
3 Calvin Lisa
3 Doug NULL
3 Dan NULL
[/tt]


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[mattmontalto]

If a case (scsv_no) has 1 suspect and 2 victims, the the main query results show that case (scsv_no) 3 times.... I want it to show it only once.... if there is 1 suspect and 1 victim it shows that case 2 times and if 4 suspects and 2 victims, it shows 6 times... In all cases, I just want to see one instance of each case (scsv_no)... thanks so much for your help.... I have put together a screen shot of the results of the query run in server mgmt studio, and have tried to attach it to this message... let me know if you can see it... if not, maybe I can email it to you directly?

the results show the foreign keys and how they relate to the main_tbl.... thanks again....

Matt

 

[mattmontalto]

Attempting to post screen shot again

 

[gmmastros]

Since you are showing suspects and victims, but only want to show the case once, how do you decide which suspect and which victim to show if there are multiples? I mean... is there something in the data to indicate which one?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[mattmontalto]

actually, in the web application, i am not showing the victim or suspect field, but I have to select them in the main query so that they are searchable since the users sometimes want to search by a victim or suspect name... I dont know if you are familiar with it, but I am using ASP runner to generate the code to make this a web usable database....

 

[SQLScholar]

Matt,

What is really required is a quick table showing us what output you want to see? As if your just typing it in excel.

Its difficult from your discription to see how you visualise bringing 2/3/4 (or whatever) different pieces of data into one field.

Dan

----------------------------------------

http://www.fountain.me.uk

Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Dr. Seuss

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------

 

[gmmastros]

I am not familiar with ASP Runner. What confuses me.... why would you need to return data that you won't be showing? Is this a requirement for ASP Runner?

What worries me is.... what if you had 1 million cases with an average of 1.5 suspects and 2 victims per case? This would return (at a minimum) 1 million rows, and a maximum of 2 million rows. What happens to this data? Is it cached in IIS so that you can search through the data? With all those columns, you could end up with 50 megabytes of data for each user that connects to your web site.

Instead, you will probably be better of writing your own search stored procedure. It will be long and complicated, but it will also give you the best performance and the least amount of memory usage (resulting in better scalability).

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[mattmontalto]

Well Dan... basically I want to see a listing of all cases (scsv_no), but I dont want to see duplicated entries of the same case just because there are multiple victims or defendants

so the results would be the same as if I were only running a select all against the main_tbl without the joins to the suspect and victim table....

the trouble is that for the web application, I have to use this query selecting everything from all 3 tables so that the users' searches are run against all the tables... and then the results show multiple instances of the same case number.

I know this is rather an annoying issue... as the query itself is actually doing exactly what is asked of it... it is providing all instances where there is a case with either a suspect or a victim... I just am trying to figure out a way to make it only show distinct case numbers in the results...

and, by the way, I did play with the DISTINCT function and I now understand that the function works on the query as a whole and not on individual fields (columns)...

 

[mattmontalto]

George,

it is somewhat of a requirement of asp runner in as much as I wrote in the above response to Dan.... that I need to search all three tables so I need to select all the fields I want to be able to search from in my main query... I just want it to only return results that have distinct case #'s

 

[gmmastros]

try this for your FROM clause.

FROM dbo.main_tbl
     LEFT JOIN (Select dbo.victim_tbl.main_tbl_id,
                       Min(dbo.victim_tbl.id) As Id
                From   dbo.victim_tbl
                Group By dbo.victim_tbl.main_tbl_id) As FirstVictim
       ON dbo.main_tbl.id = FirstVictim.main_tbl_id
     LEFT OUTER JOIN dbo.victim_tbl 
       ON dbo.victim_tbl.main_tbl_id = FirstVictim.main_tbl_id
       And dbo.victim_tbl.id = FirstVictim.Id
     LEFT JOIN (Select dbo.suspect_tbl.main_tbl_id,
                       Min(dbo.suspect_tbl.id) As id
                From   dbo.suspect_tbl
                Group By dbo.suspect_tbl.main_tbl_id
                ) As FirstSuspect
       ON dbo.main_tbl.id = FirstSuspect.main_tbl_id
     LEFT OUTER JOIN dbo.suspect_tbl 
       ON dbo.suspect_tbl.main_tbl_id = FirstSuspect.main_tbl_id
       And dbo.suspect_tbl.id = FirstSuspect.Id
ORDER BY dbo.main_tbl.scsv_no DESC, dbo.main_tbl.id DESC

Note that this should return the first victim and the first suspect. You cannot return multiple victims and/or suspects and still only return one case.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Qik3Coder]

You said you need all the data in the query coming back for the search, but the problem is in the display of duplicate data.

What language are you writing in to present the data on the website? If it's a .Net based language on at least 2.0 you should be able to use LINQ to thin the dataset prior to presenting it.


You can't thin the source result set, without reducing the number of possible hits from a search.

Lodlaiden

I'll answer your question, not solve your problem

 

[mattmontalto]

Ladies and gentlemen.... I believe I have found another genius!... George... that is fantastic! So that i understand.... this query will return every case #, but only the first victim ?... and if there is no victim, then only the first suspect?

George... you are a genius... and I commend you on your skill... this is precisely the reason why I direct people to this forum and tell them that this is positively the best and most helpful forum that I have ever encountered on the web... Maybe someday I can help you out.... I am mostly a server and network guy... but also have many years of desktop support experience as well.... (fairly new to sql though)

But if ever I can do something to help you, please dont hesitate to ask....

Dan ... thank you as well... your time and effort are also greatly appreciated....

Sincerely

Matt Montalto
New York, NY

 

[mattmontalto]

Thanks Qik3Coder... I am using a 3rd party program to generate the asp code for the site.... and I am not all that familiar with writing asp code, so I think I will have quite a bit of difficulty implementing your suggestion, but thank you very much for your help.

 

[Qik3Coder]

I normally just drop stuff like this, but medical records (and now law) posts, tweak a nerve.

Thinning a rowset that contains suspects/victims, so that only the first suspect ever entered will come up in a search will drastically reduce your ability to cross reference.

You may want to look at creating a view that crosstabs all the victims names into a single column, and all the suspect names into a single column, then join on the view. This will give you a complete search and still reduce your result set to a single row.

Lodlaiden

I'll answer your question, not solve your problem

 

[mattmontalto]

thanks quik3.... George... thanks again, but unfortunately ASP runner will not function right when I use that query...

qik3 --- thanks very much for your advice, but I am not sure that I know how to follow your directions there exactly...

 

[Qik3Coder]

How flexible is the searching provided by the ASP Runner tool?
Is it one field and it searches all of the datafields? or does it dynamically generate search boxes, so you can do things like "Victim=Male" & "SuspectAge>35"

If this uses a single search then you will wind up crosstabbing the results. You will have results like
case[tab]Victim[tab][tab][tab][tab][tab][tab][tab]Suspect
X[tab][tab]{Mr. Plum} {Ms. White}[tab]{Mr. Green} {Scarlet O'Hara}

If it allows flexible searching, then you are stuck as you will need to modify the generated output, though there should be some option for that in the ASP Runner set up.

Lodlaiden

A lack of experience doesn't prevent you from doing a good job.

 

[mattmontalto]

i believe it offers both options for search...