SQL Error

[johnrb87]

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

[sinista]

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
";
?>

[johnrb87]

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

[sinista]

did you get the same error or something completely different?

[johnrb87]

seems to be the same error

[johnrb87]

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?

[sinista]

<?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

[sinista]

<?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