Another (stupid) SQL question!

cherrio · 10-11-2008 12:25PM #1

Hey guys,

What I am trying to do is a search on users "profile". So if one of the values matches, that all rows with the same "user_id" are returned.

I know it can be done in two straight queries, first search for value, then search on returned user_id, but can it be done in 1 search? Bonus question would be which way would be faster, 1 'complex' query or two shorter ones?

Thanks in advance!

CREATE TABLE IF NOT EXISTS `fieldvalues` (
  `id` int(11) NOT NULL auto_increment,
  `fieldname_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `value` varchar(255) collate latin1_general_ci NOT NULL,
  PRIMARY KEY  (`id`)
)

Query 1
SELECT `Fieldvalue`.`id`, `Fieldvalue`.`fieldname_id`, `Fieldvalue`.`user_id`, `Fieldvalue`.`value` FROM `fieldvalues` AS `Fieldvalue` WHERE `Fieldvalue`.`value` LIKE "%Some text%"

Query 2
SELECT `Fieldvalue`.`id`, `Fieldvalue`.`fieldname_id`, `Fieldvalue`.`user_id`, `Fieldvalue`.`value` FROM `fieldvalues` AS `Fieldvalue` WHERE `Fieldvalue`.`user_id` = user_id from query 1

kayos · 10-11-2008 12:39PM

SELECT 
	FV.id, 
	FV.fieldname_id, 
	FV.[user_id], 
	FV.[value] 
FROM 
	fieldvalues AS FV 
	JOIN (SELECT 
			[user_id]
		  FROM 
			fieldvalues
		 WHERE 
			[value] LIKE "%Some text%"
		) AS D ON FV.[user_id] = d.[user_id]

seamus · 10-11-2008 12:43PM

At first I thought that the first query will return all of the data that you need, but I see what you're doing.

The easiest way to do this is using subqueries, assuming that you're using mysql:

SELECT 
	`Fieldvalue`.`id`, 
	`Fieldvalue`.`fieldname_id`, 
	`Fieldvalue`.`user_id`, 
	`Fieldvalue`.`value` 
FROM 
	`fieldvalues` AS `Fieldvalue` 
WHERE 
	`Fieldvalue`.`user_id` IN (
				SELECT 
					`Fieldvalue`.`user_id` 
				FROM 
					`fieldvalues` AS `Fieldvalue` 
				WHERE 
					`Fieldvalue`.`value` LIKE "%Some text%"
			)

cherrio · 10-11-2008 01:34PM

Works perfect, thx guys.

Another (stupid) SQL question!

Comments