how do I select multiple conditions from 1 table column?
I am trying to select all books whose authors last names begin with I, J or K. I have 3 tables: book, author and book_author. The following query works with one condition but not with three. SELECT * FROM book WHERE id IN (SELECT bookID FROM book_author WHERE authID IN (SELECT author.id FROM author WHERE LEFT(last_name, 1 ) = 'I')); This does not work: SELECT * FROM book WHERE id IN (SELECT bookID FROM book_author WHERE authID IN (SELECT author.id FROM author WHERE LEFT(last_name, 1 ) = 'I' LEFT(last_name, 1 ) = 'J' LEFT(last_name, 1 ) = 'K')) ; But this produces irrational results - there are no author names with the last names starting with I, J or K. SELECT * FROM book WHERE id IN (SELECT bookID FROM book_author WHERE authID IN (SELECT author.id FROM author WHERE LEFT(last_name, 1 ) = '$Auth' LEFT(last_name, 1 ) = '$Auth1' LEFT(last_name, 1 ) = '$Auth2')) ; I'm a little lost here. Could somebody explain, please? -- unheralded genius: A clean desk is the sign of a dull mind. - Phil Jourdan --- p...@ptahhotep.com http://www.ptahhotep.com http://www.chiccantine.com/andypantry.php -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: how do I select multiple conditions from 1 table column?
On Wed, Apr 1, 2009 at 1:27 PM, PJ af.gour...@videotron.ca wrote: I am trying to select all books whose authors last names begin with I, J or K. I have 3 tables: book, author and book_author. The following query works with one condition but not with three. SELECT * FROM book WHERE id IN (SELECT bookID FROM book_author WHERE authID IN (SELECT author.id FROM author WHERE LEFT(last_name, 1 ) = 'I')); This does not work: SELECT * FROM book WHERE id IN (SELECT bookID FROM book_author WHERE authID IN (SELECT author.id FROM author WHERE LEFT(last_name, 1 ) = 'I' LEFT(last_name, 1 ) = 'J' LEFT(last_name, 1 ) = 'K')) ; But this produces irrational results - there are no author names with the last names starting with I, J or K. SELECT * FROM book WHERE id IN (SELECT bookID FROM book_author WHERE authID IN (SELECT author.id FROM author WHERE LEFT(last_name, 1 ) = '$Auth' LEFT(last_name, 1 ) = '$Auth1' LEFT(last_name, 1 ) = '$Auth2')) ; I'm a little lost here. Could somebody explain, please? Maybe the LIKE operator would be sufficient: SELECT * from book WHERE last_name LIKE I% OR last_name LIKE J% OR last_name LIKE K%; David
Re: how do I select multiple conditions from 1 table column?
David Giragosian wrote: On Wed, Apr 1, 2009 at 1:27 PM, PJ af.gour...@videotron.ca mailto:af.gour...@videotron.ca wrote: I am trying to select all books whose authors last names begin with I, J or K. I have 3 tables: book, author and book_author. The following query works with one condition but not with three. SELECT * FROM book     WHERE id IN (SELECT bookID     FROM book_author WHERE authID IN (SELECT author.id http://author.id/     FROM author WHERE LEFT(last_name, 1 ) = 'I')); This does not work: SELECT * FROM book     WHERE id IN (SELECT bookID     FROM book_author WHERE authID IN (SELECT author.id http://author.id/     FROM author WHERE LEFT(last_name, 1 ) = 'I' LEFT(last_name, 1 ) = 'J' LEFT(last_name, 1 ) = 'K')) ; But this produces irrational results - there are no author names with the last names starting with I, J or K. SELECT * FROM book     WHERE id IN (SELECT bookID     FROM book_author WHERE authID IN (SELECT author.id http://author.id/     FROM author WHERE LEFT(last_name, 1 ) = '$Auth' LEFT(last_name, 1 ) = '$Auth1' LEFT(last_name, 1 ) = '$Auth2')) ; I'm a little lost here. Could somebody explain, please? Maybe the LIKE operator would be sufficient:  SELECT * from book  WHERE last_name LIKE I% OR last_name LIKE J% OR last_name LIKE K%;  David  Sorry bout it all. Ifigured it out and it is the OR that was not there = the AND just plain does not work! Thanks, though. -- unheralded genius: A clean desk is the sign of a dull mind. - Phil Jourdan --- p...@ptahhotep.com http://www.ptahhotep.com http://www.chiccantine.com/andypantry.php -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
