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 ?

J-16 SDiZ · Accepted Answer · 2009-07-08 06:48:12Z

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 8, 2009 at 6:48

J-16 SDiZ

27k4 gold badges67 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

opHasNoName Over a year ago

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

Sergey 'm17' Kolosov · Accepted Answer · 2009-07-09 08:59:02Z

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] …

Collectives™ on Stack Overflow

Zend_DB_Select - How to write this SQL Statement

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related