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:
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.
c.company_id as 'Company Id'
, c.company_proper_name as 'Company Legal Name'
, c.company_short_name as 'Company'
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
co.company_id = c.company_id
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
p.country_iso_code = 'CA'