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.
