CODEWITHSUNDEEP
mysqljoinjoomla
Anatol
Anatol

Reputation: 2043

Joomla DB Query with join over three tables

in my Joomla 2.5 Template I´ve using following JOIN query to get some infos out of two tables (#_content and #_attachments)

    ->select(array('a.description','a.display_name','a.parent_id','a.filename','a.url', 'b.title', 'b.id','b.state','c.title'))
    ->from('#__attachments AS a')
    ->join('INNER', '#__content AS b ON (a.parent_id = b.id)')
    ->where("b.state = 1")
    ->order("RAND()");

How can I add one more join for table #_categories to get also the categories title (here namend as 'c.title'.)? The Id of the categories row is saved as catid in #_content. I expect the assignment should look something like:

->join('INNER', '#__content AS b ON (c.id = b.catid)')

Thanks,

tony

Upvotes: 0

Views: 2852

Answers (1)

Valentin Despa
Valentin Despa

Reputation: 42622

You need to add:

->join('INNER', '#__categories AS c ON b.catid = c.id')

Please consider adding an LEFT JOIN for attachments IF it's possible that an item in content has no attachment and using as the main table content

Upvotes: 2

Related Questions