[Resolved] MySQL agro

[sicranmer]

Little background,

I am trying to identify which tax zone an orders country falls into. Problem is that I need to check both the delivery and billing zones. If I run the query linking just one country (delivery) to the zone table it all works. When I add the second (billing) country link I only get the billing data returned in both columns.

ie
1.
[FONT=courier new]
delivery_country  countries_id  zone_id  
United Kingdom      222             2 
United Kingdom      222             2 
United Kingdom      222             2 
Greece               84             2 
Germany              81             2 
United Kingdom      222             2 
United Kingdom      222             2 
United Kingdom      222             2
United Kingdom      222             2 
United Kingdom      222             2 
Turkey              215          NULL 
United Kingdom      222             2 
Turkey              215          NULL
[/FONT]

2.
[FONT=courier new]
delivery_country  countries_id  zone_id  billing_country  countries_id  zone_id  
United Kingdom      222           NULL   United States      223         NULL 
United Kingdom      222           NULL   United States      223         NULL 
United Kingdom      222           NULL   United States      223         NULL 
Greece              84               2   United Kingdom     222            2 
Germany             81               2   United Kingdom     222            2 
United Kingdom      222              2   Portugal           171            2 
United Kingdom      222              2   Ireland            103            2 
United Kingdom      222              2   France              73            2 
United Kingdom      222           NULL   Channel Islands    240         NULL 
United Kingdom      222           NULL   Channel Islands    240         NULL 
Turkey              215           NULL   Australia           13         NULL            
United Kingdom      222           NULL   Australia           13         NULL 
Turkey              215           NULL   Australia           13         NULL       
[/FONT]

selects are
1.
SELECT
o.delivery_country,
c1.countries_id,
z1.zone_id
FROM orders o
LEFT JOIN countries AS c1 ON c1.countries_name = o.delivery_country
LEFT JOIN zones AS z1 ON z1.zone_country_id = c1.countries_id
where IFNULL(o.billing_country,'') != ''
and o.delivery_country != o.billing_country
ORDER BY o.billing_country DESC

2.
SELECT
o.delivery_country,
c1.countries_id,
z1.zone_id,
o.billing_country,
c2.countries_id,
z2.zone_id
FROM orders o
LEFT JOIN countries AS c1 ON c1.countries_name = o.delivery_country
LEFT JOIN countries AS c2 ON c2.countries_name = o.billing_country
LEFT JOIN zones AS z1 ON z1.zone_country_id = c1.countries_id
LEFT JOIN zones AS z2 ON z2.zone_country_id = c2.countries_id
where IFNULL(o.billing_country,'') != ''
and o.delivery_country != o.billing_country
ORDER BY o.billing_country DESC

Any ideas 😕

[tomhath]

If the listing you posted came from php, the problem could be that you have the same column name repeated, give the columns an alias using the "as" keyword:

SELECT 
o.delivery_country, 
c1.countries_id as c1country, 
z1.zone_id as z1zone, 
o.billing_country, 
c2.countries_id as c2country, 
z2.zone_id as z2zone

[sicranmer]

SHWEEET tomhath,

You are da man. Thats all it was. The files are all linked correctly its just the output needed asliases. must remenber this for the future.

cheers

Si.