jonny
(Jonny)
1
Hi,
I’m trying to execute this query in querybuilder.
SELECT id, title, area_id From item WHERE item.area_id IN (SELECT item.area_id FROM item WHERE item.id = 3)
I’ve read the docs for the Where() In operator but i’m still having some trouble getting it to execute. What would be the correct way?
Thanks
Jonny
instead of this
SELECT id, title, area_id From item WHERE item.area_id IN (SELECT item.area_id FROM item WHERE item.id = 3)
can you try this?
SELECT
id, title, area_id
FROM
item a
INNER JOIN item b
ON a.area_id =b.id
WHERE
b.id = 3
jonny
(Jonny)
3
Thanks for the reply, yours worked in my SQL client. But I seem to be having a real problem with backticks (I think).
CDbCommand:
) failed: SQLSTATE[42000]: Syntax error or access violation:
1064 You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near
'.title, i
.area_id, i
.slug
…
SELECT i
.id,
AS i
.title, i
.area_id, a
.slug
FROM item
i
JOIN item
b
ON i.area_id = b.id
WHERE i.id=:id.
Is how it was being executed.
I had the same outcome when trying to use this method http://www.yiiframework.com/forum/index.php/topic/26020-yii-query-builder-how-to-construct-in-condition-mix-with-other-and-condition/
jonny
(Jonny)
4
I removed the $cmd->select as an array and just quoted it like:
$cmd->select = ‘i.id, i.title, i.slug’;
Hope that help anyone else.