CODEWITHSUNDEEP
mysqlselectwhere-clause
DrNoFruit
DrNoFruit

Reputation: 185

mysql select rows based on more than one criteria and linked to another column

I'm having some trouble working this one out... any suggestions would be very welcome.

I have a table such as the following:

collection_id    book_id    author
===================================  
    1             027       derek
    2             090       bob
    2             102       andy 
    2             102       bob

Now I want to retrieve the book_id for a book that I know belongs in collection 2 and is authored by andy AND bob (i.e. 102).

A query that includes a WHERE clause for andy AND bob returns nothing because obviously there is only one author per row. A query that includes a WHERE clause for andy OR bob returns the book_id for 090 and 102 because bob is the author of book 090 too.

How would you go about obtaining the right book_id for the book authored by andy AND bob?!

Many thanks for your thoughts!

Upvotes: 0

Views: 224

Answers (4)

jjones150
jjones150

Reputation: 302

How about something like the following:

SELECT book_id FROM test_books WHERE collection_id='2' AND author IN ('andy', 'bob') GROUP BY book_id HAVING count(*) > 1

Upvotes: 1

Zohaib
Zohaib

Reputation: 7116

SELECT *
FROM (SELECT *
      FROM table
      WHERE author = 'andy') a
     (SELECT *
      FROM table
      WHERE author = 'bob') b
WHERE a.book_id = b.book_id

Upvotes: 0

Pete
Pete

Reputation: 10918

Select *
from collection
where author = 'andy' and book_id in (
    Select book_id from collection where author = 'bob'
)

Upvotes: 0

codeling
codeling

Reputation: 11408

That can only be solved using subqueries. For example, take a look at the EXISTS keyword.

SELECT DISTINCT book_id FROM book_table b
    WHERE exists (SELECT 1 FROM book_table b2
            WHERE b2.book_id=b.book_id AND b2.author='andy')
        AND exists (SELECT 1 FROM book_table b3
            WHERE b3.book_id=b.book_id AND b3.author='bob')
        AND collection_id=2

or, simplified (but not symmetrical):

SELECT book_id FROM book_table b
    WHERE exists (SELECT 1 FROM book_table b2
            WHERE b2.book_id=b.book_id AND b2.author='andy')
        AND author='bob'
        AND collection_id=2

Alternatively, use COUNT in a subquery:

SELECT book_id FROM book_table b
    WHERE (SELECT COUNT(1) FROM book_table b2
            WHERE b2.book_id=b.book_id AND b2.author='andy') > 0
        AND author='bob'
        AND collection_id=2

Upvotes: 1

Related Questions