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

[bizzydint]

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

 

I'm 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. (it's 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 :P

 

 

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 :P

 

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 i'm 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.xtremedotnettalk.com/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 I'd have to use the SQL usercursor option which I'm hoping I can avoid by using a funky .NET control.

 

Robby: atm, I've 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]

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

 

I'm 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 don't 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 isn't 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.

 

I'm 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 can't 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]

I'm a little confused on exactly what you're trying to do. Your title says one thing, but your description says another.

 

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

[Robby]

Hmm, I've forgotten the title, now I'm confused as well.

[bizzydint]

cant think of how explain it more clearly...but Liqdfire's 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 i'm trying to avoid!

 

anyway, i'll 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 I'm 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

 

There's also DISTINCT which eliminates duplicate rows.

 

SELECT DISTINCT * FROM authors

 

If you want further help you'll need to give us further details. We're 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.

 

I'm quite happy with all the DB stuff! Its just the display side of things....

 

When I find time, I'll have a go at the method in Liqdfire's link (unfortunately got 101 other things to do first)

 

ps. I'm 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)

 

It's 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 I'm making sense. Feel free to bug me if I havent.

[JIMcFadyen]

Do you need to similar queries to get the data? Wouldn't one simlpe DISTINCT query for "Categories" and a more detailed query for "Items" suffice?

 

This would have slightly increases overhead, but not as much as 2 full selects.

 

Maybe I've just lost the plot...

 

John

[Robby]

bizzydint, I'll test out Part 14 on the weekend and let you know. I does look promising though.

[wyrd]

*still confused* :confused:

 

JIMcFadyen; you aren't alone.

 

If the topic starter could show us some sample data in his tables, then show us a sample output, it'd help.

[jfackler]

bizzydint,

Seems like a right join should create a resultant table with multiple null values in your left most column. Seems like the most efficient means of getting the results you want. You can then utilize a method of formating a cell in the datagrid based on it's value, i.e. if the cell is a null, blank it. Here's a link to how to do just that.

 

http://www.syncfusion.com/FAQ/WinForms/FAQ_c44c.asp#q745q

 

Look at item 5.10. I think it'll do the trick.

 

Jon

[Robby]

From what I understand this is what he want it to look like...

http://aspnet.4guysfromrolla.com/images/md.ss.jpg