Hi, I have a problem with my search script. I usally find all my answers just taking time to search in the forum, but this time I'm stuck. I'm not sure what to search for so I havent found the answer yet. So hope any of you can help me.
I have some tables (I use mySQL) I wan't to get search results from, but I have some problems. I'll show you an example of my problem.
Here is some test tables:
CREATE TABLE name(
nameID INT NOT NULL AUTO_INCREMENT,
name VARCHAR(30),
PRIMARY KEY(aID)
);
CREATE TABLE namehobby(
nameID INT NOT NULL ,
hobbyID INT NOT NULL ,
PRIMARY KEY(nameID,hobbyID)
);
CREATE TABLE hobby(
hobbyID INT NOT NULL AUTO_INCREMENT,
hobby VARCHAR(30),
PRIMARY KEY(hobbyID)
);
INSERT INTO name VALUES('1','John')
INSERT INTO name VALUES('2','Doe')
INSERT INTO hobby VALUES('1','Football')
INSERT INTO hobby VALUES('2','Hockey')
INSERT INTO namehobby VALUES('1','1')
INSERT INTO namehobby VALUES('1','2')
INSERT INTO namehobby VALUES('2','1')
I want to get a result that returns all fields from table NAME that have both Football and Hockey as hobby. Meaning that namehobby is 1,1 and 1,2.
I tryed have tryed this:
SELECT name FROM name,namehobby WHERE name.nameID = namehobby.nameID AND hobbyID = 1 AND hobbyID = 2;
But I know this doesn't work, but I hope that helps showing what I'm trying to do.
I was hoping there was a way to do this directly in mySQL to make it as fast as possible. If there isnt a way to do it in mySQL, I'm concidering to do multiple queries and then match them with PHP.
