select rows from table1, based on values in table2

[Guest twinpawer]

I have two tables, one named businesses and one named categories. Now, a business may have multiple categories (a one-to-many relationship). The following is their basic structure:Businesses-------------ID, NameCategories-------------ID,BusinessID,CategoryNow, I need to get a list of rows from the business table, according to whether they have a say, BOTH a category named 'Hardware', and a category named 'Software'. I cannot do it with a normal 'Select businesses.* from businesses,categories where categories.businessID = businesses.id and category = 'hardware' and category = 'software', because that would return no results since a category cannot have both values at a time. Any idea how I can make an SQL query to load the needed data?Listed below are a test tableBusinesses=========ID| Name------------ 1 | Peter's Place 2 | Ron's Computers 3 | The Pizza Shop 4 | The IronmongeryCategories==========ID | BusinessID | Title-------------------------- 1 | 1 | Hotel 2 | 2 | Hardware 3 | 2 | Software 4 | 3 | Food 5 | 4 | Hardware----------------------------------The needed query in this case, if i'm looking for both hardware and software would return business row with id 2, thus Ron's Computers. Note that 'The Ironmongery' must not be returned, since it only has the category Hardware, and does not have Software too.It sounds simple, but I can't find a way to do it!Thanks!

[justsomeguy]

You didn't say which database you're using, but if it supports subqueries you can do this:

SELECT * FROM businesses WHERE id IN (SELECT businessid FROM categories WHERE title='hardware' INTERSECT SELECT businessid FROM categories WHERE title='software')

or

SELECT * FROM businesses WHERE id IN (SELECT businessid FROM categories WHERE title='hardware') AND id IN (SELECT businessid FROM categories WHERE title='software')