Q: mySQL - use outer SELECT value in subselect in SELECT clause I have this query:
SELECT c.id, p.id, p.register_date, o.office_agency_name_1, a.street_address, a.supplemental_address_1, a.city, s.abbreviation, co.iso_code, a.postal_code, ph.phone, pf.phone, e.email,
ab.name, c.addressee_custom, (Select phone from civicrm_phone where contact_id = civicrm_contact.id  AND is_billing = 1) as billing_phone 

FROM   civicrm_contact c,
   civicrm_participant p,
   civicrm_value_office_info_1 o, 
   civicrm_address a,
   civicrm_state_province s, 
   civicrm_country co,
   civicrm_phone ph, 
   civicrm_phone pf,

   civicrm_email e,          
   civicrm_address ab



WHERE  p.contact_id = c.id
  AND  p.is_test    = 0
  AND  p.event_id = 1 
  AND  p.status_id NOT IN (4,11,12)
  AND  ( c.is_deleted = 0 OR c.is_deleted IS NULL )
  AND  o.entity_id = c.id
  AND  a.contact_id = c.id
  AND  s.id = a.state_province_id 
  AND  co.id = a.country_id 
  AND  ph.contact_id = c.id 
  AND  ph.is_primary = 1 
  AND  pf.contact_id = c.id 
  AND  pf.phone_type_id = 3
  AND  e.contact_id = c.id 
  AND  e.is_primary = 1
  AND  ab.contact_id = c.id
  AND  ab.is_billing = 1 

billing_phone is the most recent addition ... this is of course not working and giving me:
1054 - Unknown column 'civicrm_contact.id' in 'where clause' 
Due to the complexity, and the fact that there are more fields yet to add, I'd like to avoid JOINs if possible, and stay as close to the current syntax as possible. 
When I tried to add billing phone with the same pattern as the others, it became apparent that not everyone has a billing phone - the number of rows dropped dramatically.
I'd like to make this work as a subquery in the outer SELECT (which would get me NULLs for those rows with no billing phone, right?).
Actually, it doesn't work as a JOIN either:
SELECT c.id, p.id, p.register_date, o.office_agency_name_1, a.street_address, a.supplemental_address_1, a.city, s.abbreviation, co.iso_code, a.postal_code, ph.phone, pf.phone, e.email,
ab.name, c.addressee_custom, pb.phone as billing_phone 

FROM   civicrm_contact c,
       civicrm_participant p,
       civicrm_value_office_info_1 o, 
       civicrm_address a,
       civicrm_state_province s, 
       civicrm_country co,
       civicrm_phone ph, 
       civicrm_phone pf,

       civicrm_email e,          
       civicrm_address ab

LEFT JOIN civicrm_phone as pb on pb.contact_id = c.id  AND pb.is_billing = 1 

WHERE  p.contact_id = c.id
  AND  p.is_test    = 0
  AND  p.event_id = 1 
  AND  p.status_id NOT IN (4,11,12)
  AND  ( c.is_deleted = 0 OR c.is_deleted IS NULL )
  AND  o.entity_id = c.id
  AND  a.contact_id = c.id
  AND  s.id = a.state_province_id 
  AND  co.id = a.country_id 
  AND  ph.contact_id = c.id 
  AND  ph.is_primary = 1 
  AND  pf.contact_id = c.id 
  AND  pf.phone_type_id = 3
  AND  e.contact_id = c.id 
  AND  e.is_primary = 1
  AND  ab.contact_id = c.id
  AND  ab.is_billing = 1

That gives me:
1054 - Unknown column 'c.id' in 'on clause'
How do I make this work?

A: What it appears is that the aliased table "c" (which refers to civicrm_contact) doesn't have a column "id" in it.
Are you sure this column exists in the table?
