Thank you Brenton, thank you Colin :-D I used left join with the same logic and it does the trick.
Again thanks Colin Guthrie-6 wrote: > > 'Twas brillig, and Brenton Alker at 23/06/09 04:22 did gyre and gimble: >> -----BEGIN PGP SIGNED MESSAGE----- >> Hash: SHA1 >> >> awesome wrote: >>> I am new in Zend and relational database design. >>> >>> I have two tables that I would like to join in one so that certain >>> values >>> from 1st table are replaced with associated values from another table. >>> >>> This query works: >>> SELECT a.iditems, a.model, >>> (SELECT name FROM headings WHERE idheadings = a.heading1) as >>> heading1Name, >>> (SELECT name FROM headings WHERE idheadings = a.heading2) as >>> heading2Name >>> from items as a; >>> >>> I would like to to this kind of query with zend... is it possible? >> >> What you're doing is subselects, not joins. You can do subselects in ZF >> by creating them as separate selects then combining them. But I don't >> think you actually need them in this case. Guessing based on the query, >> I think this SQL is equivalent: >> >> SELECT items.iditems, items.model, h1.name as heading1Name, h2.name as >> heading2Name >> FROM items >> INNER JOIN headings AS h1 ON h1.idheadings = items.heading1 >> INNER JOIN headings AS h2 ON h2.idheadings = items.heading2 > > I *think* (not tried) that the first statement will return nuls when no > match is found in the subselect, so this is more like a left join rahter > than an inner join. > > Logic still applies, but just s/inner/left/. > > >> Which can be done in ZF like: >> >> $table = new Model_DbTable_Items(); >> $select = $table->getAdapter()->select() >> ->from('items', array('iditems','model')) >> ->joinInner(array('h1' => 'headings'), 'h1.idheadings = >> items.heading1', array('heading1Name' => 'name')) >> ->joinInner(array('h2' => 'headings'), 'h1.idheadings = >> items.heading2', array('heading2Name' => 'name'))) >> ->where('iditems = ?', '1'); >> $rows = $table->fetchAll($select); >> >> (Untested) >> >> - -- >> >> Brenton Alker >> PHP Developer - Brisbane, Australia >> >> http://blog.tekerson.com/ >> -----BEGIN PGP SIGNATURE----- >> Version: GnuPG v1.4.9 (GNU/Linux) >> Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org >> >> iEYEARECAAYFAkpASnAACgkQ7bkAtAithuuoVgCfcMS8QKR1iuHOnK5UaBp3bFKM >> shoAoJi67Mpm+Mo06XNo8/EvvHyMRAxt >> =TTYe >> -----END PGP SIGNATURE----- >> > > > -- > > Colin Guthrie > gmane(at)colin.guthr.ie > http://colin.guthr.ie/ > > Day Job: > Tribalogic Limited [http://www.tribalogic.net/] > Open Source: > Mandriva Linux Contributor [http://www.mandriva.com/] > PulseAudio Hacker [http://www.pulseaudio.org/] > Trac Hacker [http://trac.edgewall.org/] > > > -- View this message in context: http://www.nabble.com/Zend-joined-tables-query-tp24158616p24163487.html Sent from the Zend Framework mailing list archive at Nabble.com.