Is there a better way other than CROSS JOIN?

Databases
1

Hi,

Just a check if I am doing these joins in the best way.

I am mostly curious about having to use the CROSS JOIN to make it work.

I have to join several tables together to get the complete data. The main issue I faced with this query is neigther the company or address tables have INNER, OUTER, or LEFT JOIN potential via the ‘ON’ clause.


SELECT
    c.company_short_name as 'Company'
    , CONCAT(
        a.street_number 
        , ' ' 
        , a.street_name 
        , ', ' 
        , p.city_name
        , ', ' 
        , p.state_prov_abbr
        , ', ' 
        , ct.country_name
        )
        AS 'Address'
FROM 
    addresses as a
CROSS JOIN companies as c
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
INNER JOIN companies2addresses as c2a
    ON c2a.company_id = c.company_id
    And c2a.address_id = a.address_id

it outputs something like:

[TABLE]
[TR]
[TD]Company[/TD]
[TD]Address[/TD]
[/TR]
[TR]
[TD]ABC Building[/TD]
[TD]11 Somewhere Rd., ACity, AProvince, ACountry[/TD]
[/TR]
[TR]
[TD]Bob’s Athletics[/TD]
[TD]12 NotHere Ct., ACity, AProvince, ACountry[/TD]
[/TR]
[/TABLE]

It produces the proper data but is there a better way to do this?


<!-- Select a specific compaies address -->    
SELECT
    c.company_short_name as 'Company'
    , CONCAT(
        a.street_number 
        , ' ' 
        , a.street_name 
        , ', ' 
        , p.city_name
        , ', ' 
        , p.state_prov_abbr
        , ', ' 
        , ct.country_name
        )
        AS 'Address'
FROM 
    addresses as a
INNER JOIN companies as c
    ON c.company_short_name = 'ABC Building'
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
INNER JOIN companies2addresses as c2a
    ON c2a.company_id = c.company_id
    And c2a.address_id = a.address_id

It ouputs somethin like:

[TABLE]
[TR]
[TD]Company[/TD]
[TD]Address[/TD]
[/TR]
[TR]
[TD]ABC Building[/TD]
[TD]11 Somewhere Rd., ACity, AProvince, ACountry[/TD]
[/TR]
[/TABLE]

I guess that I never expected to have use a CROSS JOIN?

Regards,
Steve

2

the CROSS JOIN is definitely wrong

and this –

FROM 
    addresses as a
INNER JOIN companies as c
    ON c.company_short_name = 'ABC Building'

is the equivalent of a cross join, joining all addresses (i.e. addresses for all companies) to that one company

what you want is this –

  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 c.company_short_name = 'ABC Building'

note especially that the FROM clause begins with the most restricted table, i.e. the only table that has a WHERE condition

this ensures that all the other tables that are joined onto the company table are joining only to one company’s row (unless there is more than one company with that particular short name)

note also how each join’s ON clause references a column from the table being joined, and makes it match a column from a previously-mentioned table

3

Wow Rudy, this works much better - Thanks!

You maybe wouldn’t believe how long I tried to do what you showed but kept running into ‘unknown column’ errors.

It does make great sense in the way that you build from the previously mentioned table.

That CROSS JOIN did smell bad to me although the test data is so small I wouldn’t see the impact of joining all addresses for all companies to that one company, until the data grew a production level!

Regards,
Steve

4

thanks

always start with the most restricted table, and then when you join, always connect to a previously mentioned table

this strategy will let you write queries easily, without semantic errors (e.g. unknown column), and the FROM clause will likely mirror the EXPLAIN, which makes optimization way easier too