eliminating duplicates from stored procedure joins
-
David,
true. I was assuming that the tables don't contain duplicate rows, but are in the usual kind of many-to-one (master-detail, parent-child) relationship.
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant -
Yes that was beginning to confuse me as well. It would be useful to know if
Case_no# can be in both Case30 and CASEOLDR?
Case30 has only one row per Case_no#
Case30 has more than one row per Case_no#
CASEOLDR has only one row per Case_no#
CASEOLDR has only more than one row per Case_no#
Data is duplicated in tblFileNames (Case_no#,reportid,type)
Data is duplicated in userviewed (viewid,reportid)
maybe darshit_99 can answer this?
Far away is close at hand in the images of elsewhere.
Anon. -
hi schwab and burrows,
Friends u are right. I am into fun trap. But believe me its no fun to be here in trap. Listen David I tried the solution u gave me in stored procedure but i dont know why it doesnot return any recordset. Can u tell me why?
-
Hi David,
If i execute this statement then i get duplicate values but in the same statement if i take out f.reportid and u.viewid then i dont get duplicate values but the thing is that i need those values and so when i keep it in Select Statements then i get duplicates. If u can suggest what to do.
(SELECT DISTINCT Case30.Case_no#,Case30.Last_name,Case30.[First Name],Case30.Determination,Case30.Sender,Case30.[Case Type],Case30.Date,Case30.[Qwik Due],Case30.[Qwik sent date],Case30.[Final due],Case30.[Final sent date],Case30.[on hold code],f.reportid,u.viewid FROM Case30 Right Outer JOIN tblFileNames f ON f.case_id = Case30.Case_no# Right Outer JOIN userviewed u ON u.reportid = f.reportid'+@return+@where_clause+@return+
'UNION' +@return+
'SELECT DISTINCT CASEOLDR.Case_no#,CASEOLDR.Last_name,CASEOLDR.[First Name],CASEOLDR.Determination,CASEOLDR.Sender,CASEOLDR.[Case Type],CASEOLDR.Date,CASEOLDR.[Qwik Due],CASEOLDR.[Qwik sent date],CASEOLDR.[Final due],CASEOLDR.[Final sent date],CASEOLDR.[on hold code],f.reportid,u.viewid FROM CASEOLDR Right Outer JOIN tblFileNames f ON f.case_id = CASEOLDR.Case_no# Right Outer JOIN userviewed u ON u.reportid = f.reportid)c' +@return+@where_clause
-
hey,
This is my exact problem. If anybody can help out? In my Case30 and Caseold tables all the values are unique and so not a problem but the tblfilenames tables have duplicate caseno.s ,In tblfilenames I have unique reportids but when it si joined to userviewed which has different viewids for same reportids because of which it considers it as to be dufferent for the same caseno. I want one Caseno. to be displayed just once in a resultset instead of getting displayed several times due to having different reportids and viewids for the same caseno.
I hope smebody can help? I have gone mad figuring this out?
-
Data is duplicated in tblFileNames (Case_no#,reportid,type)
Data is duplicated in userviewed (viewid,reportid)
Data is not duplicated in Case30 and caseold
-
I tried this too
elect @sql = 'SELECT c.Case30.Case_no#,c.Last_name,c.[First Name],c.Determination,c.Sender,c.[Case Type],c.Date,c.[Qwik Due],c.[Qwik sent date],c.[Final due],c.[Final sent date],c.[on hold code],f.reportid,f.type,u.viewid FROM
(SELECT DISTINCT c.*FROM (SELECT DISTINCT Case30.Case_no#,Case30.Last_name,Case30.[First Name],Case30.Determination,Case30.Sender,Case30.[Case Type],Case30.Date,Case30.[Qwik Due],Case30.[Qwik sent date],Case30.[Final due],Case30.[Final sent date],Case30.[on hold code] FROM Case30 '+@return+@where_clause+@return+
'UNION' +@return+
'SELECT DISTINCT CASEOLDR.Case_no#,CASEOLDR.Last_name,CASEOLDR.[First Name],CASEOLDR.Determination,CASEOLDR.Sender,CASEOLDR.[Case Type],CASEOLDR.Date,CASEOLDR.[Qwik Due],CASEOLDR.[Qwik sent date],CASEOLDR.[Final due],CASEOLDR.[Final sent date],CASEOLDR.[on hold code] FROM CASEOLDR'+@return+@where_clause+@return+')c
Right Outer JOIN tblFileNames f ON f.case_id = c.Case_no# Right Outer JOIN userviewed u ON u.reportid = f.reportid) ' +@return
But it gives me error Line 10: Incorrect syntax near ')'.And if i remove the bracket then i get some other error.What should i do?
-
You may be pleased (kind of!) to know that the reason you have had so much trouble is that what you are trying to do is logically impossible.
You don't have a fan trap - but you do have a standard chain of three tables.
You need to be aware of which table is at the 'top' of the chain.
This is the Case table.
The chain goes from one to many:
.CASE
.
.associated with many
.
...REPORTS
...
...viewed in many
...
......USERVIEWINGS.
Here is an example assuming only one 'case' table, and using inner joins for simplicity:
The tables:
CASE:
id
--
.1
.2
REPORT:
id....CaseID
--....------
.1.........1
.2.........1
.3.........2
.4.........2
USERVIEWING:
id....RepID
--....-----
.1........1
.2........1
.3........2
.4........2
.5........3
.6........3
.7........4
.8........4
The SELECT statement:
select
C.id as Cid,
R.id as Rid,
U.id as Uid
from CASE C
join REPORT R
on R.CaseID = C.id
join USERVIEWING U
on U.RepID = R.id
The results:
Cid Rid Uid
..1...1...1
..1...1...2
..1...2...3
..1...2...4
..2...3...5
..2...3...6
..2...4...7
..2...4...8
As you can see, we can't return fewer records than the number of matching records in the bottom-most table (USERVIEWING.)
In your case, you are using outer joins, so you are likely to return even more records (i.e. even unmatched records from the higher tables.)
This is unavoidable.
If you have a one-to-many relationship between two tables, but you only want one record per record from the topmost (one) table, you have to decide what do do with the extra information from the 'child' (many) tables.
Often you will use an aggregate function like 'SUM', 'COUNT' or MAX to combine many values into one.
Alternatively you might do something more comlpicated, and either create a crosstab (so that by using extra columns you can show the multiple values on one line), or you might combine all the values into a single field, separated by commas, say.
The last two are more complex, and I wouldn't recommend either if you can avoid it.
It might be useful to hear from you what the query is being used for, as I think we need to understand your requuirements in order to decide what you need to do.
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant -
Spot on Tim
Far away is close at hand in the images of elsewhere.
Anon. -
Thats it guys,
Is there no way out that i can get unique values through query.Is there any way if i can get rid of that duplicates through ASP where I am calling this procedure?
-
The reportid and viewid are mainly used for linking the files with the resultset for user to view those files.
-
You need some sort of hierarchical display for the data: you could try outputting as XML, and using an XSL stylesheet to display the data (or for intranet on IE, use an XML data island and javascript) - or most simply, just have one page for the Case IDs, and when the caseID is clicked, open a new page (and a new query) showing the relevant files for viewing.
How are you displaying the data at present? If you are using ADO scroll through the recordset and build an HTML table dynamically, you could have a variable to check when CaseID changes, and replace the value of CaseID with " " for all rows except the first of each CaseID. You will still have the same number of rows, but it will be easier for users to read and easier to find the CaseID they are looking for if the table is like:
CASEID.......FILENAME
....23.......File1
.............File2
.............File4
....30.......File3
.............File7
.............File8
.............File9
....48......File12
Hope these rather sketchy remarks are of some use.
Tim
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant -
DOH! In the preceding post, "&.n.b.s.p" (without dots) was rendered as " ".
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
Viewing 13 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply