Subquery

[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
)

[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).

[eluser]dekimpeb[/eluser]
If you mean by doing $this-DB-query, ive already tried that but it didn't work

[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.

[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
)

[/quote]

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
)

[eluser]dekimpeb[/eluser]
Nevermind did it with $this->db->query, i just called the wrong variables in the view.