SQL query help

Omio · #1 April 2, 2010, 04:29 PM

What would be the query for this :-
" Produce a list of all books having the same title. The list should include the title, ISBN, year of publication, type of binding and the language of the book."

*AsifTheManRahman* · #2 April 2, 2010, 04:53 PM

SELECT title, ISBN, yearOfPublication, typeOfBinding, language FROM Books ORDER BY title

...assuming the name of your table is "Books". Alternatively you can use GROUP BY (as opposed to ORDER BY). Is that what you're looking for?

*AsifTheManRahman* · #3 April 2, 2010, 05:10 PM

In fact, a better way to do this might be:

SELECT title, ISBN, yearOfPublication, typeOfBinding, language FROM Books WHERE title in (SELECT title FROM Books GROUP BY title HAVING (COUNT(title) > 1))

...assuming you're looking for duplicates.

Omio · #4 April 2, 2010, 07:19 PM

Quote:

Originally Posted by AsifTheManRahman
In fact, a better way to do this might be:

SELECT title, ISBN, yearOfPublication, typeOfBinding, language FROM Books WHERE title in (SELECT title FROM Books GROUP BY title HAVING (COUNT(title) > 1))

...assuming you're looking for duplicates.

Yay, Its work.
Thanks a lot Asif vai.

Omio · #5 April 2, 2010, 07:55 PM

One more:
How will I count multiple column from same table?
For instance,
Library item can be borrowed under borrowing category of short loan and ordinary loan. How would I produce a list of all library item under that category. And output will be the name of category and total number of item.

So, I have the table name "Borrowing_catg". This are the four collumn: b_catg_id, Shortloan_id, ordinaryL_id and library_item.