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
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’).
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 = [COLOR=#ff0000]c.company_id[/COLOR]
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
Whoops found the problem I was referencing
INNER
JOIN companies2communications as c2com
ON [COLOR=#ff0000]c.company_id[/COLOR] = c2com.company_id
Which should have been on the sub-query
INNER
JOIN companies2communications as c2com
ON [COLOR=#ff0000]co.company_id[/COLOR] = 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 = [COLOR=#ff0000]44[/COLOR]
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