[CS-FSLUG] MySQL Challenge with JOINS

[Timothy Butler]

Hi everyone,
	I'm trying to find a way to be able to do a join where I select  
items from one table only if two items from a second table fit  
certain criteria.

	For example:

	table 1:

	item number     title
	1		linux
	2		windows
	3		mac os x

	


	table 2:

	item number    feature
	1		mouse support
	1		open source
	2		mouse support
	3		mouse support


	Basically what I want is to select the item from table 1 when both  
"mouse support" and "open source" are assigned to that item number in  
table 2.

	If I only want to select by mouse support, I have a query that can  
do that:

	SELECT table1.* FROM table1 LEFT JOIN table2 ON table2.itemnumber =  
table1.itemnumber WHERE table2.itemnumber = "mouse support";


	Essentially I want that where clause to have an "and  
table2.itemnumber = 'open source'" but if I feed such an AND to MySQL  
it thinks I expect the same row to have the column equal two  
different things, which of course is impossible. Is there anyway to  
say 'WHERE one row from table2.itemnumber = "mouse support" AND  
another row from table2.itemnumber = "open source"'?

	Thanks,
		Tim