SQL Error

Good day all

I have the following code

SELECT items.id, items.title, items.price, (SELECT value FROM new WHERE id = items.id) AS value1 FROM myitems items JOIN people p ON p.person_id = items.id WHERE items.active = 1 AND ((items.title LIKE '%apple%') || (value1 LIKE '%apple%')) ORDER BY items.id ASC

but I keep getting

#1054 - Unknown column 'value1' in 'where clause'

But i'm not sure why this is happening, as value1 exists

Can anyone help?

Thanks

not to sure if it will work, but how about this?

<?php
$sql="
SELECT 
	items.id, 
	items.title, 
	items.price, 
	v.value as value1
FROM
	(SELECT value FROM new WHERE id = items.id) v, 
	myitems items JOIN people p ON p.person_id = items.id 
WHERE 
	items.active = 1 
	AND ((items.title LIKE '%apple%') || (v.value LIKE '%apple%')) 
ORDER BY 
	items.id ASC
";
?>

Thanks, but that didn't seem to do the job, any other ideas?

did you get the same error or something completely different?

seems to be the same error

I guess the issue is with

AS value1

linking into

value1 LIKE '%apple%'

does something just need to be edited with those bits of the query?

<?php
$sql="
SELECT 
	items.id, 
	items.title, 
	items.price, 
	v.value
FROM
	(SELECT value FROM new WHERE id = items.id) v, 
	myitems items JOIN people p ON p.person_id = items.id 
WHERE 
	items.active = 1 
	AND ((items.title LIKE '%apple%') || (v.value LIKE '%apple%')) 
ORDER BY 
	items.id ASC
";
?>

dunno really its hard to debug SQL, without a database

<?php
$sql="
SELECT 
	items.id, 
	items.title, 
	items.price, 
	v.value
FROM
	(SELECT value FROM new WHERE id = items.id) v, 
	myitems items JOIN people p ON p.person_id = items.id 
WHERE 
	items.active = 1 
	AND ((items.title LIKE '%apple%') || (v.value LIKE '%apple%')) 
ORDER BY 
	items.id ASC
";
?>

 

dunno really its hard to debug SQL, without a database

I know i wrote the quoted code but if it was me i think i would look at  WHERE id = items.id) i would try replacing the item_id for an actual value, then i would take the OR out then see if it works, you just gotta play with SQL sometimes