Zend_DB_Select - How to write this SQL Statement

Question

Good Morning,

i have some Trouble with the Zend Framework and Zend_DB_Select, i want to use this (Working) SQL Statement as Zend DB Select Statement :

select
  oslang
from
  oslang, os
where
  oslang.oslang_id = os.oslang_id and
  ossubversion_id = 1

I have tryed to following but it doenst work :

try {
        $select = $this->_db->select()
             ->from('OSLANG')
             ->from('OS')
             ->where("OSLANG.OSLANG_ID = OS.OSLANG_ID")
             ->where("OSSUBVERSION_ID = ?", $subVersionId);
        $results = $select->query()->fetchAll();
    } catch (Exception $e) {
        $this->_logException($e);
        $results = array();
    }

it must be possible that more than one OS is associated with an OSLANG_ID

Does someone has an nice idea where my error is ?

score 2 · Accepted Answer · answered Jul 08 '09 at 06:48

2

        $select = $this->_db->select()
             ->from('OSLANG')
             ->join('OS',"OSLANG.OSLANG_ID = OS.OSLANG_ID")
             ->where("OSSUBVERSION_ID = ?", $subVersionId);
        $results = $select->query()->fetchAll();

answered Jul 08 '09 at 06:48

J-16 SDiZ

26,473
4
65
84

Not 100% ;-) It must be possible to select from OS all Records associated with the OSLANG_ID. There are X Operating Systems associated to the same OSLANG_ID horrible i now ;) – opHASnoNAME Jul 08 '09 at 08:58

Sergey 'm17' Kolosov · Answer 2 · 2009-07-09T08:59:02.393

SELECT * FROM x, y

is equal to

SELECT * FROM x CROSS JOIN y

So, try using joinCross() method. According to its PHPDoc:

The $name and $cols parameters follow the same logic as described in the from() method.

It 'll be something like that:

    $select = $this->_db->select()
         ->from('OSLANG')
         ->joinCross('OS')
         ->where("OSLANG.OSLANG_ID = OS.OSLANG_ID")
         ->where("OSSUBVERSION_ID = ?", $subVersionId);
    $results = $select->query()->fetchAll();

Note, if you are using MySQL, CROSS JOIN is equal to INNER JOIN:

In MySQL, CROSS JOIN is a syntactic equivalent to INNER JOIN (they can replace each other). In standard SQL, they are not equivalent. (MySQL 5.1 Reference Manual :: JOIN Syntax)

The only difference is that:

INNER JOIN is used with an ON clause, CROSS JOIN is used otherwise

So in MySQL you can omit using INNER or CROSS keywords, the syntax is:

… tableReference [INNER | CROSS] JOIN tableFactor [joinCondition] …

Zend_DB_Select - How to write this SQL Statement

2 Answers2