Jump to content

select rows from table1, based on values in table2


Guest twinpawer
 Share

Recommended Posts

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!

Link to comment
Share on other sites

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')

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...