How do I force the display to not show duplicate data?

[bizzydint]

Surely that subject makes no sense, but I have more space here....

Im learning on the fly and cant work out which control I should use to create the following display.

The DB will return a "list" of authors and book titles, where one author may have more than one book. (its actually a many to many)
i.e.
Fred Bloggs 1001 ways to skin a cat
Albert Humperdinck My lunch has gone cold
Mary Dooberry Say My Name B****
Mary Dooberry Cant think of a decent title so


And for obvious reasons, I want it displayed as:
Fred Bloggs :
1001 ways to skin a cat
Albert Humperdinck :
My lunch has gone cold
Mary Dooberry :
Say My Name B****
Cant think of a decent title so

But due to the complications behind the scenes, I cant just pull out all the authors then a separate query to find the books that they are linked to. It all comes out of one query.


Forgive me if im being dense, but PLEASE can someone point me in the right direction?

cheers

 

[zy_abc]

Hello,

I think you are also facing a similar problem like i had. But the thing is instead of "author" it is "file_id" and "book titles" it is "version number". I re-designed the page as Nersus recommended and i am quite happy with result.

Here is the link: http://www.computerhelp.forum/showthread.php?threadid=72263

 

[Robby]

Is it really a many-to-many? Can one book have multiple authors?

Are you using a Select statement, or a DataSet/DataTable to display them seperatly?

 

[bizzydint]

Not a many-many anymore...I realised it was a bit shoddy, so the DB table is now a one-many relationship.

zy_abc: thanks, but I cant change the db design so Id have to use the SQL usercursor option which Im hoping I can avoid by using a funky .NET control.

Robby: atm, Ive got a DataList with a source coming from a SQLDatareader. The data is sorted but the author names are getting repeated.


Any suggestions greatly appreciated (within reason!)

 

[JIMcFadyen]

I would suggest you look at producing the table through code, looping down the DataReader and checking the author name against the previous, inserting a blank cell if it duplicates.

John

 

[bizzydint]

Im guessing that would be pretty slow, wouldnt it? (Im not entirely sure how efficient .net is??)

Im looking at anything up to 5000 records being generated here...

 

[zy_abc]

If you can give an example of how your select statement is going to be then we can try to give you an idea.

Also, why dont you try Left Join and Inner Join in your select statement to get one row of records. It might do the trick

 

[JIMcFadyen]

As a thought, it might be an idea to look at an XML solution listing the author and all their books. If the data isnt going to be changed too regularly you could re-create this at regular intervals.

Also, .net is alot more efficient than ASP 3.0, but 5000 records is going to be slow no matter what. Have you considered using paging?

John

 

[bizzydint]

Definitely would be paging it (am yet to work out how that whole shabang works but it looks simple enough ;-))

Data will be changing frequently. unfortunately.

oh well. Back to the drawing board

 

[JIMcFadyen]

If you load the data into a DataSet and work with a disconnected model it will give you greater flexibility. You could then produce a list of unique authors by querying the first dataset, which would probably be useful.

Im not sure how this would look on your site, but the closest I can think is if you had a paged datalist of authors, with each author containing another datalist for books.

John

 

[Liqdfire]

Your best bet is to do a master / detail data grid

http://aspnet.4guysfromrolla.com/articles/040203-1.aspx

 

[Robby]

Why cant you use an Inner Join like the following?

"SELECT     Parent.ID,Parent.Author, Child.Book, Child.description " & _
"FROM        ParentAuthors INNER JOIN " & _
"                 ChildBooks ON ParentAuthor.id = ChildBooks.id"

 

[wyrd]

Im a little confused on exactly what youre trying to do. Your title says one thing, but your description says another.

If you can explain a bit more clearly Im sure we can come up with a single SQL statement (or control) to get what youre looking for.

 

[Robby]

Hmm, Ive forgotten the title, now Im confused as well.

 

[bizzydint]

cant think of how explain it more clearly...but Liqdfires link seems very promising, so hopefully we can end the debate.

Robby - that SQL will return a list with the author names repeated, which is exactly what im trying to avoid!

anyway, ill see how it goes and fill you in later

 

[Robby]

I think I know what you want to display....

Author1
Book1
Book2

Author2
Book3
Book4

Author3
Book5
Book6
Book7
Book8

Instead of this method....

Author1... Book1
Author1...Book2

Author2...Book3
Author2...Book4

Author3...Book5
Author3...Book6
Author3...Book7
Author3...Book8

 

[wyrd]

I think Im understanding now...

Bad database design aside (you should have a seperate table with author names and ids to avoid duplicate names) you can use GROUP BY..

SELECT * FROM authors GROUP BY author_name

Theres also DISTINCT which eliminates duplicate rows.

SELECT DISTINCT * FROM authors

If you want further help youll need to give us further details. Were shooting in the dark here. Can you give us full details on your table(s) like what columns they contain and what the constraints are, then give us an example of information you want to extract.

 

[Robby]

Wyrd, I think you missunderstood, his DB design is fine (I think), it is the way that he wants to display the resultset to the user that needs to be in that fashion (or so).

Is this correct bizzydint?

 

[bizzydint]

yep, tis true.

Im quite happy with all the DB stuff! Its just the display side of things....

When I find time, Ill have a go at the method in Liqdfires link (unfortunately got 101 other things to do first)

ps. Im not a bloke! But I wont get all girlie and be offended by the assumption ;-)

 

[bizzydint]

Back again, having had a proper look at the article "DataGrid Web Control: Part 14, Part 2" (http://aspnet.4guysfromrolla.com/articles/040203-1.2.aspx)

Its not quite right because in the article, it uses FAQ categories with sub FAQ items listed. There are 2 separate DB queries, one to retrieve the FAQ Categories, then one to retrieve all the FAQ items.

However, in my application I need to choose a select group of "Categories" and "Items". The ones chosen depend on both the category and item, so unless I call 2 very similar queries to pull out the data twice (which will be *far* too slow - trust me!), is there a way of using the master/detail display from one query source?

I hope Im making sense. Feel free to bug me if I havent.