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

Status
Not open for further replies.
Do you need to similar queries to get the data? Wouldnt 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 Ive just lost the plot...

John
 
bizzydint, Ill test out Part 14 on the weekend and let you know. I does look promising though.
 
*still confused* :confused:

JIMcFadyen; you arent alone.

If the topic starter could show us some sample data in his tables, then show us a sample output, itd help.
 
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 its value, i.e. if the cell is a null, blank it. Heres 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 itll do the trick.

Jon
 
I am not permitted to show any data/code/sql as I am dealing with sensitive information (NB: authors/books was just a simplified example)

jfackler - right join didnt work. thanks for the suggestion though ;-)

wyrd - no offense, but it might be time for you to give up. I really cant explain the problem more than I have! sorry...


Ive been thinking it through again, and I may have a solution that will do - it uses 2 queries, one to pull out the "books" and then one to pull out the "authors" of those "books". Not as efficient, but I cant seem to find another way....


ANyone know how to pass a column from a DataTable to a db SP?
query 1 = select bookName, Author_iD form Books where "blah"
query 2 - select DISTINCT AuthorName from Authors where Author_ID in (results from query 1)
I only want query 1 to be called once!!

Alternatively, is there a way of selecting the DISTINCT authors from the DataTable???


Really appreciate all the effort you guys are making....


But *please* stop insisting that Im male ;-)
 
I am not permitted to show any data/code/sql as I am dealing with sensitive information (NB: authors/books was just a simplified example)
Click to expand...

As you notice I said sample data. Who says it has to be the actual data youre using?

wyrd - no offense, but it might be time for you to give up. I really cant explain the problem more than I have! sorry...
Click to expand...

*shrug* ... Well if you dont want my help you dont have to read my posts. ;)

query 1 = select bookName, Author_iD form Books where "blah"
query 2 - select DISTINCT AuthorName from Authors where Author_ID in (results from query 1)
I only want query 1 to be called once!!
Click to expand...

SELECT DISTINCT AuthorName FROM Authors WHERE Author_ID IN (SELECT BookName, Author_ID FROM books WHERE blah)

Once again this probably isnt what you want, but its at least in one query.

BTW why is DISTINCT needed? Shouldnt author names already be unique?

Alternatively, is there a way of selecting the DISTINCT authors from the DataTable???
Click to expand...

I dont believe you can, you need to query the database to get DISTINCT authors from a table.

Really appreciate all the effort you guys are making....
Click to expand...

Oh? But I thought you wanted me to give up...

If you arent finding us of much help, go to the news group microsoft.public.sqlserver.programming and post there (tons of professionals who do nothing but SQL programming go to this). Be warned though; unless youre willing to show them what your table schema is and sample data and a sample of what you want selected, you may not find much help.
 
Last edited by a moderator:
Right - am making decent progress on all this on my own now, but will attempt to give fictional data/structure again....just incase you can come up with any bright ideas.


Tbl_Author
author_id INT
author_name NVARCHAR
author_otherInfo DATATYPE


tbl_Books
book_ID
Book_Name
author_id
book_genre INT
book_price
book_something


The program wants to display a certain set of books, eg depending on book_genre

If you pull it all out of one query and display it, it comes out as:

Author 1 Book A
Author 1 Book B
Author 2 Book C
Author 3 Book D
Author 3 Book E

To make it look nicer, we want:

Author 1
Book A
Book B

Author 2
Book C

Author 3
Book D
Book E


As no one could come up with a way of generating that from one set of query results - Im using 2 (required for the control to work - not cos i cant do a nested select!)
- One to pull out the books
- One to pull out the authors of *those* books


However - its a bit inefficient. Im pulling out the books. Then concatenating the AUTHOR_IDs into a string which I pass to the 2nd query.

Is there a way of just chucking the results of the first query straight to the second one???

I cannot (repeat *cannot*) run the "getBooks" query again in the "getAuthors" query - its far too chunky to be done twice.


Ive said all this before. Chances are wyrd will still find it vague ;-)
 
Okay, now I understand. The master/detail form is what you want, unfortunately its not a single select statement. I do not know how to do this in a singe select statement (or even if thats possible).

URL of live demo of master/detail:
http://aspnet.4guysfromrolla.com/demos/MasterDetail.aspx

From the sounds of it, your two selects should probaby be;
SELECT * FROM authors ORDER BY name
SELECT * FROM books WHERE author_id=? ORDER BY name

Im still confused as to why you need DISTINCT. From the table design you showed me, authors should be unique to begin with.

If its sluggish then you need to change your design a little bit (or do some performance tuning on your database). And dont be rolling your eyes at me either, there shouldnt be any reason for two simple select statements to be sluggish.

Idea:
- Put authors in a drop down list. If there are tons of authors then you can seperate them further into "pages" of A-Z. User clicks B, the drop down fills up with all authors names starting with B.

- When the user selects an author from the drop down list, select the books that belong to that author into either a data grid or data list and display them.

- Be sure to use paging for the book list.

The layout should look something like this (which slightly resembles the output you showed me, just one author at a time)

Selected Author (Drop down list of authors)

(DataGrid or DataList of books of selected author)
Selected Author Book 1
Selected Author Book 2
Selected Author Book 3
etc...

Quick index tips to speed up queries:
- If you are going to select books and/or authors by name 90%+ of the time (ORDER BY name), then make the name column the clustered index for the table. This will improve performance for large selections drastically.

- Make sure you have a foreign key relationship with the author_id column in books to the author_id column in authors. This can improve performance for JOINs.

- Make sure the author_id and book_id are marked as primary keys.
 
Im with wyrd on this one, sounds like your database needs performance tuning if its sluggish because of too small queries.

I can return a massive resultset with no problem, as the table has been indexed correctly.

Too carry out in one query try using xml, and then populate the results you want from that.

As to everything else it looks like you need to decide on the display mechanism you want, suggestions by jfackler look like what you describe, if you cant do exactly what you need just inherit a datagrid and make it do what you want or make your own control.
 
You dont seem to have caught onto the fact that Ive been simplifying the query so that it makes sense to you. The *actual* problem doesnt even involve books and authors!


The first query actually does:

Code: 
SELECT Author_ID, tbl_Books.book_id, tbl_Book.Book_Name
FROM tbl_Books
WHERE 
author_ID IN ( +@comparisonString + )  AND

Book_Criteria1 IN  ( + @criteriaMatch + )  AND
Book_Criteria2 IN  ( + @criteriaMatch + )  AND
Book_Criteria3 IN  ( + @criteriaMatch + )  AND
Book_Criteria4 IN  ( + @criteriaMatch + )  AND 

(
Book_Criteria5 =  + @ID +  OR
Book_Criteria6 =  + @ID +  OR
Book_Criteria7  =  + @ID +  OR
Book_Criteria8  =  + @ID +  
)
ORDER BY Author_ID)

Where @comparisonString is a list of integers,
and @criteriaMatch is a list of strings (~300 char long)

TBl_Books currently contains 82645 rows - and were still adding to it. Itll probably get up to 100,000 rows.

We may also have several 1000s of users running these queries at the same time.


SO even with very efficient DBing, you can see why Im trying to cut down elsewhere.
 
bizzydint it comes down to efficient design, you need to think how many master and child records can they see at a time. Not 82645! If you write your query correctly and your database is indexed properly you can return this information in I estimate under a second.

It seems the way you are trying to accomplish this is extremly inefficient. Im guessing you get results of the first query and go through each one and the associated books.

No wonder you are experiencing problems....

Set a number of authors they can see at any one time, use paging on your DataGrid, and only query the books when the user requests to see that information.

If you really need to see everything in one go, use xml.
 
but the authors that get displayed depend on the books meeting the criteria. so i cant limit the authors first....
 
So let me get this right; the user queries a book and gets an author back strange (very stange) ah well.
 
You dont seem to have caught onto the fact that Ive been simplifying the query so that it makes sense to you. The *actual* problem doesnt even involve books and authors!
Click to expand...

:mad: *extremely disgusted*

You ask our help based on information you give us, yet you dont even give us the correct information in which you need help on. How exactly did you expect us to help you? :confused:

Now since youve shown us what youre really trying to do, could you show us what the following is AND give us an example of what it can be based on a fictitional search;

@comparisonString
@criteriaMatch
@ID
Book_Criteria1-8 (if these are table columns then their names are way too cryptic)

In the mean time, you can speed up IN by providing values in ascending order.

Just to make sure I understand; Youre searching for books that meet a certain criteria, the displaying the authors for those books (but only displaying the authors name once if they happen to be the author of multiple books found)

With only 100k rows, you shouldnt be getting any performance hits at all. Id hate to see what happens when youre list reaches 1 million.
 
Last edited by a moderator:
OK, read this entire topic four times now, and come to the conclusion bizzydint must be doing something drastically wrong.

Looking at the first query and simplifying aside
You dont seem to have caught onto the fact that Ive been simplifying the query so that it makes sense to you. The *actual* problem doesnt even involve books and authors!
Click to expand...

Your query shouldnt take long, I work for a software house developing financial solutions and implemented the sort of thing you want on a table of 1/2 million records, properly indexed and it doesnt take excessive amounts of time.

You may find better performance if you dont ask the database server to order by AuthorID as the table will be ordered by BookID (dependent on how optimised) and that could affect query.

Second query with your list of AuthorID why do you need to query again what else do you need the name get in the first query. OK if you cant do that how many records can they see at a time 50...100...150 doubt it; dont get all the authors. Say your query returned 1000 author ID and you sent all of them to the second query (how long would that take) unless.... you are querying the database authorID by authorID... now that would cause major problems (please say your not doing that!)

If the database is slow there are ways of splitting a nested query into two DataSets programatticaly.

[optimising]
Say you could see 50 records and you performed a query on the database to get the name of 50 authors how long would that take 1/2 second at the most!

Out of interest you say your getting slow performance how slow? What does profiler say you are getting for the first query?

Anything more than 5 seconds you are doing something wrong [complicated database aside] the tests I performed were on a complex financial database.
 
Last edited by a moderator:
with the customers i deal with, 5 seconds is too long ;-)
(yes, i know...but you cant argue with them!)


And now im going to put a stop to this thread (on my part at least). Im not getting any help from it anymore, and its wasting all of our time....

Thanks for trying.
 
And now im going to put a stop to this thread (on my part at least). Im not getting any help from it anymore, and its wasting all of our time....
Click to expand...

We gave you quite a bit of help, its up to you on whether you make use of it or not. If you would of fully explained from the beginning we could of given you even more. *shrug*

Good luck with your project.
 
Status
Not open for further replies.
Back
Top