Correlated Sub Query in Select Problem

[ServerStorm]

Hi,

I have a query that I would like the company_id from the outer Select for use in the subquery, but no matter what I try I get 'Unknow column 'c.company_id' in 'on clause'. I have read the MySQL documentation and I think that I am doing it the way they specify, but I might not have interpreted it correctly. Can you see why this doesn't work or how it can be done?

SELECT
  c.company_id as 'Company Id'
  , (SELECT
       p.phone_number
     FROM 
       phone_numbers as p
     WHERE
       p.company_id = c.company_id) as 'Phone Number'
  , a.address as 'Address'
FROM
  companies as c
INNER 
  JOIN companies2addresses as c2a
    ON c2a.company_id = c.company_id
INNER
  JOIN addresses as a
    ON a.address_id = c2a.address_id;

Thanks
Steve

[guido2004]

I would do it like this

SELECT
    c.company_id as 'Company Id'
  , p.phone_number as 'Phone Number'
  , a.address as 'Address'
FROM
  companies as c
INNER 
  JOIN companies2addresses as c2a
    ON c2a.company_id = c.company_id
INNER
  JOIN addresses as a
    ON a.address_id = c2a.address_id
INNER
  JOIN phone_numbers as p
    ON p.company_id = c.company_id

But if the error says the problem lies in the ON clause, then it isn't your subquery. There is no ON clause in your subquery. Are you sure you have a company_id column in the companies table? (I myself would have a column named 'id').

[ServerStorm]

Hi guido2004,

I appreciate the re-write into another join; although I am not able to do this as the query is actually a lot more complex and to get the phone and fax numbers I have to do some somewhat beefy inner joins themselves. I simplified what I showed as I was trying to gauge if I was using the correlated query in the right way?

This is the whole query:

         SELECT
           c.company_id as 'Company Id'
           , c.company_proper_name as 'Company Legal Name'
           , c.company_short_name as 'Company'
          ,(SELECT
              com.phone_number
            FROM 
              companies as co
            INNER JOIN companies2communications as c2com
                ON c.company_id = c2com.company_id
                INNER JOIN communications as com
                ON com.communication_id = c2com.communication_id
            INNER JOIN communication_types as cts
                ON cts.communication_type_id = com.communication_type_id
            INNER JOIN communication_categories as ccat
                ON ccat.communication_category_id = cts.communication_category_id
            WHERE
                co.company_id = c.company_id
            AND
              ccat.category = 'phone') as 'Phone Number'
          , a.street as 'Address'
          , a.apt_unit_number as 'Apt/Unit#'
          , p.city_name as 'City'
          , p.state_prov_abbr as 'State/Province'
          , a.post_code as 'Postal Code'
          , ct.country_name as 'Country'
        FROM companies as c
        INNER JOIN companies2addresses as c2a
          ON c2a.company_id = c.company_id
        INNER JOIN addresses as a    
          ON a.address_id = c2a.address_id
        INNER JOIN postal_codes p
          ON p.postal_code = a.post_code
        INNER JOIN countries as ct
          ON ct.country_iso_code = p.country_iso_code
        WHERE
          p.country_iso_code = 'CA'

I am sure that I have a company_id in the companies table. I agree that it should change to an id instead; however at this stage it is not possible to rename this or any other of the id columns as it will impact too many other scripts.

Regards,
Steve

[ServerStorm]

Whoops found the problem I was referencing

 INNER 
   JOIN companies2communications as c2com
     ON c.company_id = c2com.company_id

Which should have been on the sub-query

 INNER 
   JOIN companies2communications as c2com
     ON co.company_id = c2com.company_id

This approach won't work though as the subquery is returning multiple-rows and it is not unless a specific company_id is specified in the WHERE that it returns 1 result

...
,(SELECT
              com.phone_number
            FROM 
              companies as co
            INNER JOIN companies2communications as c2com
                ON co.company_id = c2com.company_id
                INNER JOIN communications as com
                ON com.communication_id = c2com.communication_id
            INNER JOIN communication_types as cts
                ON cts.communication_type_id = com.communication_type_id
            INNER JOIN communication_categories as ccat
                ON ccat.communication_category_id = cts.communication_category_id
            WHERE
                co.company_id = 44
             AND
              ccat.category = 'phone') as 'Phone Number'
           , ...

It would need to be able to use the company_id for each row returned by the outer row... now this means lots of extra php joining arrays of data together, YUCK!

Thanks Steve