SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    Foozle Reducer ServerStorm's Avatar
    Join Date
    Feb 2005
    Location
    Burlington, Canada
    Posts
    2,699
    Mentioned
    89 Post(s)
    Tagged
    6 Thread(s)

    Correlated Sub Query in Select Problem

    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?

    Code:
    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
    ictus==""

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,496
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    I would do it like this
    Code:
    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').

  3. #3
    Foozle Reducer ServerStorm's Avatar
    Join Date
    Feb 2005
    Location
    Burlington, Canada
    Posts
    2,699
    Mentioned
    89 Post(s)
    Tagged
    6 Thread(s)
    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:

    Code:
             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
    ictus==""

  4. #4
    Foozle Reducer ServerStorm's Avatar
    Join Date
    Feb 2005
    Location
    Burlington, Canada
    Posts
    2,699
    Mentioned
    89 Post(s)
    Tagged
    6 Thread(s)
    Whoops found the problem I was referencing
    Code:
     INNER 
       JOIN companies2communications as c2com
         ON c.company_id = c2com.company_id
    Which should have been on the sub-query
    Code:
     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
    Code:
    ...
    ,(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
    ictus==""


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •