+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Libraries & Helpers (https://forum.codeigniter.com/forumdisplay.php?fid=22)
+--- Thread: Subquery (/showthread.php?tid=56894)
Subquery - El Forum - 01-28-2013
[eluser]dekimpeb[/eluser]
Hey,
I got a query with subquery but I don't get how to do subqueries in CI.
This is mij query:
Code:
SELECT a.cat_name, a.cat_description, c.topic_subject, b.max
FROM tblCategories a
LEFT JOIN (
SELECT topic_cat, MAX(topic_date) AS max
FROM tblTopics
GROUP BY topic_cat
) b ON a.cat_id = b.topic_cat
LEFT JOIN tblTopics c ON (
b.topic_cat = c.topic_cat
AND b.max = c.topic_date
)Subquery - El Forum - 01-28-2013
[eluser]Aken[/eluser]
You can't, not without ugly hacks. You have to write the query yourself, or find some third party library that lets you do "subqueries" (which is actually just running the subquery first, then putting the result into the main query).
Subquery - El Forum - 01-28-2013
[eluser]dekimpeb[/eluser]
If you mean by doing $this-DB-query, ive already tried that but it didn't work
Subquery - El Forum - 01-28-2013
[eluser]Aken[/eluser]
I have no idea what "didn't work" means (was there an error? were there no results?), but that's probably a user error. Something wrong with your query, perhaps. There's no reason you can't execute a query written yourself that contains a subquery.
Subquery - El Forum - 01-29-2013
[eluser]Unknown[/eluser]
[quote author="dekimpeb" date="1359419230"]Hey,
I got a query with subquery but I don't get how to do subqueries in CI.
This is mij query:
Code:
SELECT a.cat_name, a.cat_description, c.topic_subject, b.max
FROM tblCategories a
LEFT JOIN (
SELECT topic_cat, MAX(topic_date) AS max
FROM tblTopics
GROUP BY topic_cat
) b ON a.cat_id = b.topic_cat
LEFT JOIN tblTopics c ON (
b.topic_cat = c.topic_cat
AND b.max = c.topic_date
)Hi ,
In Mysql view of try
Code:
CREATE VIEW `subQueryName` AS
SELECT a.cat_name, a.cat_description, c.topic_subject, b.max
FROM tblCategories a
LEFT JOIN (
SELECT topic_cat, MAX(topic_date) AS max
FROM tblTopics
GROUP BY topic_cat
) b ON a.cat_id = b.topic_cat
LEFT JOIN tblTopics c ON (
b.topic_cat = c.topic_cat
AND b.max = c.topic_date
)Subquery - El Forum - 01-29-2013
[eluser]dekimpeb[/eluser]
Nevermind did it with $this->db->query, i just called the wrong variables in the view.