Problem with subqueries

[ovdb]

                $reviews=mysql_query("SELECT * FROM content WHERE type='r' AND gid = (SELECT gid FROM games WHERE genre = 'Racing')")or die(mysql_error());

Why doesn't this query work?

Error message:

You have an error in your SQL syntax near 'SELECT gid FROM games WHERE genre = 'Racing')' at line 1

[edit]The field "gid" is existing in both tables; it links the two together.[/edit]

[ovdb]

this should be in the database forum shouldn't it :o

sorry :p

[Lars_Berg]

Mysql doesn't support subqueries.

[ovdb]

so how could I get the same result?

[tomhath]

No need for a subquery at all, just a simple JOIN:

SELECT * FROM content, games
WHERE content.type='r'
AND content.gid = games.gid
AND games.genre = 'Racing'

[ovdb]

cheers
guess I just overcomplicated things... as usual

But let's complicate things a bit. It's a search enigine, and it should not only search in the field genre. It has to search in the following fields:

table content:
title
date
author
intro
text

table games:
developer
publisher
genre
sound

I've tried quite a lot, but I guess I'm not using brackets correctly. I could post whatever I have, but I think it's easier for you if you could just do it from the list I gave - my query would just confuse you 🙂

Ow, and the value "racing" I gave was just an example, obviously that should be a variable. And every = should be turned into LIKE, except for the type="r" one, that should remain as it is.

[mikesgt2]

SELECT
	*,
	MATCH (
		c.title,
		c.date,
		c.author,
		c.intro,
		c.text,
		g.developer,
		g.publisher,
		g.genre,
		g.sound)
	AGAINST ('Racing') AS score
FROM
	content AS c
	LEFT JOIN games AS g ON c.gid=g.gid
WHERE
	MATCH (
		c.title,
		c.date,
		c.author,
		c.intro,
		c.text,
		g.developer,
		g.publisher,
		g.genre,
		g.sound)
	AGAINST ('Racing')
ORDER BY
	score;

Or have I got the wrong end of the stick? is this roughly what you are after. Look up fulltext search in the MySQL manual. That query would require a fulltext key on all of those fields.

Alternativly you could do this:

SELECT
	*
FROM
	content AS c
	LEFT JOIN games AS g ON c.gid=g.gid
WHERE
	c.title LIKE '%Racing%' OR
	c.date LIKE '%Racing%' OR
	c.author LIKE '%Racing%' OR
	c.intro LIKE '%Racing%' OR
	c.text LIKE '%Racing%' OR
	g.developer LIKE '%Racing%' OR
	g.publisher LIKE '%Racing%' OR
	g.genre LIKE '%Racing%' OR
	g.sound LIKE '%Racing%';

Whatever, just some ideas, i don't know if this is exactly what you want.