Select Distinct SQL problem...

[bizzydint]

Hopefully this is a really easy one and i've just missed something - but please can someone help (it's friday and my brain has given up for the weekend!)

 

 

TABLES:

tbl_Book - with a Book_ID, Book_Name, and other fields (eg genre, publisher etc)

 

tbl_Author - with Author_ID, Author_Name, and others

 

It's a many-to-many relationship, so we've got the link table

tbl_BookAuthorLink - with a Book_ID and Author_ID

 

 

OUTPUT:

List each book name once, with the name of the author - but if there is more than one author, i only want to display ONE (sounds odd I know, just trust me!)

 

The problem is that the SQL below is returning each distinct occurence of Book & Author, so if a book has 2 authors it appears twice.

 

SELECT DISTINCT Book_ID, Book_Name, Author_Name

FROM tbl_Book

INNER JOIN tbl_BookAuthorLink ON tbl_Book.Book_ID = tbl_BookAuthorLink.Book_ID

INNER JOIN tbl_Author ON tbl_BookAuthorLink.Author_ID = tbl_Author.Author_ID

Order by Book_Name

 

 

Can someone please help me work out what i'm doing!?!

 

cheers,

van

 

PS. Just for those who will get annoyed by this - yes, the books/authors thing *is* an example but it's so close to the real problem that it's not significant!

[Robby]

This should work ...

SELECT	authors.au_id, 
authors.au_lname, 
authors.au_fname, 
books.title_id, 
books.title
FROM	authors INNER JOIN	titleauthor link_table ON authors.au_id = link_table.au_id 
INNER JOIN titles books ON link_table.title_id = books.title_id

[wyrd]

DISTINCT is used for aggregate functions, like COUNT and what not. Never actually seen it used in a SELECT statement. GROUP BY is usually what's used instead.

 

Anyway, just incase Robbys solution doesn't do the trick, I'll offer up one of my own wacky ones.

 

SELECT 
  b.Book_ID, 
  b.Book_Name, 
  (SELECT TOP 1 l.Author_ID FROM tbl_BookAuthorLink l WHERE l.Book_ID = b.Book_ID) 
     AS L_Auth_ID,
  a.Author_Name 
FROM tbl_Books b
INNER JOIN tblAuthors a ON a.Author_ID = L_Auth_ID

[bizzydint]

Thanks for the help guys - but neither solution worked! :(

 

Robby - that was just the same SQL as I was already using (wasnt it?) so it returned duplicates where there were more than one author of the book.

 

Wyrd - it looked promising but i get the error:

Invalid column name 'L_AUTH_ID'.

 

ie, it doesnt seem to like joining on something that isnt an existing column. damn and blast.

 

back to the drawing board i feel....

 

but thanks :)

[Robby]

The select I used may be the same as yours (I didn't look at yours vey closely).

 

The resultset will return one of each author for each book they wrote and one of each book with its' associated authors. It will not return any duplicate row with the same book AND author, which (I think) should be your only concern, no?

 

If I'm off-track from what you need this for (which I probably am) let me know in greater detail of how you will display or use the resultset.

[wyrd]

Bizzy:

That's odd. Well that's okay, we'll just change things around then. :)

 

SELECT 
  b.Book_ID, 
  b.Book_Name, 
  a.Author_ID,
  a.Author_Name 
FROM tbl_Books b
INNER JOIN tblAuthors a ON 
  a.Author_ID = (SELECT TOP 1 l.Author_ID FROM tbl_BookAuthorLink l WHERE l.Book_ID = b.Book_ID) 

 

back to the drawing board i feel....

 

Don't give up so easily. ;)

Edited October 6, 2003 by wyrd

[wyrd]

The resultset will return one of each author for each book they wrote and one of each book with its' associated authors. It will not return any duplicate row with the same book AND author, which (I think) should be your only concern, no?

 

I don't think that was quite it, as there shouldn't be any duplicated data like that to begin with (duplicate author + book combo). It looks like Bizzy wants to select 1 of each book, and only 1 author associated with that book (even if there are multiple authors associated with it).

 

Using INNER JOIN will give duplicate book names with all the different distinct authors (which is causing the problem, as there are multiple authors associated with certain books). The only way (that I'm aware of) to get what Bizzy wants is to use a subquery so you can limit 1 author being returned for each book, and then INNER JOIN that one author with the book.

[bizzydint]

Wyrd - you're a genius....but then you probably know that already ;-)

 

as for:

Don't give up so easily.

 

when you've got work deadlines to meet, more important things to get done and people breathing down your neck, you dont waste time on getting the little things perfect.....as long as it doesnt break and isnt inefficient, it'll do until you find the time to do it properly!

 

But this is why i ask you guys - so i can get on with the hard slog and leave the intricate bits for you to solve for me ;-)

 

So thanks (to Robby as well!)

as always much appreciated. If you werent in another country, i'd give you a big hug :)

[Robby]

I'd let you if you were a girl :)

[bizzydint]

*IF* i was? what are you saying?

 

of course i'm a girl you wally! would a guy offer to hug you in return for knowledge???

:)

[wyrd]

Wyrd - you're a genius....but then you probably know that already ;-)

 

I wish.

 

when you've got work deadlines to meet, more important things to get done and people breathing down your neck, you dont waste time on getting the little things perfect.....as long as it doesnt break and isnt inefficient, it'll do until you find the time to do it properly!

 

I'm just a student, so I honestly have no idea what that's like. :(

[Robby]

*IF* i was? what are you saying?

 

of course i'm a girl you wally! would a guy offer to hug you in return for knowledge???

:)

 

Now I feel better :)