Re: [fw-general] select ... from ... where ... in

[Bill Karwin]

Dan Field wrote:
> 
> Anybody know how I might use the 'in' syntax from MySQL to produce the  
> following nested query using Zend_Db ?
> 
>  select
>  d_articleid, d_divid, d_pageid
>  from
>  wjo_div
>  where
>  d_articleid in (
>  select
>  a_articleID
>  from
>  wjo_article
>  where
>  a_articlecode like '%001' and
>  a_publicationID = 037
>  )
>  order by
>  d_pageid;
> 

You don't need to use the IN predicate.  You can do the query with a JOIN.

$select = $db->select()->distinct()
  ->from(array('d'=>'wjo_div'), array('d_articleid', 'd_divid', 'd_pageid'))
  ->join(array('a'=>'wjo_article'), "a.a_articleID = d.d_articleid",
array())
  ->where("a.a_articlecode LIKE '%001'")
  ->where("a.a_publicationID = 037")
  ->order("d.d_pageid");
$result = $db->fetchAll($select);

Regards,
Bill Karwin
-- 
View this message in context: 
http://www.nabble.com/select-...-from-...-where-...-in-tp18280581p18283075.html
Sent from the Zend Framework mailing list archive at Nabble.com.

[fw-general] select ... from ... where ... in

[Dan Field]

Anybody know how I might use the 'in' syntax from MySQL to produce the  
following nested query using Zend_Db ?


select
d_articleid, d_divid, d_pageid
from
wjo_div
where
d_articleid in (
select
a_articleID
from
wjo_article
where
a_articlecode like '%001' and
a_publicationID = 037
)
order by
d_pageid;


--
Dan Field <[EMAIL PROTECTED]>   Ffôn/Tel. +44 1970 632 582
Peiriannydd Meddalwedd Software Engineer
Llyfrgell Genedlaethol Cymru   National Library of Wales