Select distinct from a DataTable???

[bizzydint]

I've got one DB query that pulls out Authors & Books. THe relationship is one-many, so the author names get repeated.
On display, I want to show Author name once with the list of books (ie a master/detail control)

I *CANT* change the query and examples I've seen have used 2 separate queries, one for the authors and one for the books which i cant do (it's more complicated than I'm letting on)

So I need to make the display change.

Since no one could help me with thread855-551449 been rethinking how to solve the problem and...

Would there be a way to read the db results into a DataTable, then select the distinct authors from there for display?? Then I could databind that subset of data to the "master" control....

any help would be greatly appreciated!

 

[fifelo]

Just out of curiosity why can't you change the query?

 

[fifelo]

The only reason I ask is because you can use queries and what not as tables and re-select from them... you can join tables on themselves, or rejoin queries on themselves...

a simple example of using a sub query as a table

SELECT e.name, n.login
FROM (select * from employees) as e, ( select * from employees) as n
WHERE e.employee_id = n.employee_id

 

[bizzydint]

Oh yeah, i get the whole SQL thing - it's just that my query is ever so slightly "big"

I would paste it here, but it wouldnt make sense to anyone - so I'll attempt to do a simple example of what it does....

Tbl_Authors contains Author_ID, Author_Name
tbl_Books contains Book_ID, Book_Name, Author_ID, SomeCriteria

The query pulls out book names depending on SomeCriteria ie.
SELECT Book_Name, Author_Name from tbl_Books
INNER JOIN tbl_Authors ON tbl_Author.Author_ID = tbl_BOoks.Author_ID
WHERE SomeCriteria = "some hideous comparison"


Then on the display side, I want to have the author name displayed once, with a list of their books....


****And now that i've had to think it through like this, maybe I can use two queries! One to pull out the books and author_ids and then one to pull out the author names from those ids....


SO a new question arises - can I pass one column of a DataTable to a Stored Proc??? Or will I have to loop through to make it into a string??

 

[Rhys666]

How should the display look?

Author Book
Book
Book
Book
Book

...or...
Author Book Book Book Book Book
...?

Rhys
Thought out... Maybe,
Opinionated... Probably
But it is only an opinion!

 

[bizzydint]

the first-ish.

Ideally I'd like to have it as

Author Name
Book 1
Book 2
Book 3

But the control seems to want two separate columns so i'm getting

Author Name Book 1
Book 2
Book 3

 

[bizzydint]

Oh, i wish i could edit my last post now. I seem to have worked out how to display it in the same column (was very simple - ask if you want me to paste code!)

Just going to see how the rest goes.

Thanks for your suggestions/advice though!