Page 4 of 4

Re: MySQL, get joined records separated by commas

Posted: Fri Nov 28, 2008 8:33 am
by batfastad
Here's my query:

Code: Select all

SELECT `companies`.`address1`, `companies`.`address2`, `companies`.`address3`, `companies`.`company_name`, `companies`.`county_state`, `companies`.`postcode_zip`, `companies`.`town_city`, `companies`.`geo`, `contacts`.`address1_contact`, `contacts`.`address2_contact`, `contacts`.`address3_contact`, `contacts`.`contact_name`, `contacts`.`county_state_contact`, `contacts`.`mail_contact`, `contacts`.`postcode_zip_contact`, `contacts`.`reg_stamp`, `contacts`.`town_city_contact`, `contacts`.`geo_contact`, GROUP_CONCAT(`tags`.`name` ORDER BY `tags`.`name` ASC SEPARATOR ',') AS `classifications` FROM `contacts`  LEFT JOIN `countries` ON `contacts`.`geo`=`countries`.`geo` LEFT JOIN `countries` ON `companies`.`geo`=`countries`.`geo`  LEFT JOIN `companies` USING(`company_id`) LEFT JOIN `tags_data` ON `companies`.`company_id`=`tags_data`.`company_id` LEFT JOIN `tags` ON `tags_data`.`tag_id`=`tags`.`tag_id` AND `tags`.`category`='OE FRANCHISE' WHERE `contacts`.`mag_amd`='FREE' GROUP BY `contacts`.`contact_id` ORDER BY `companies`.`geo`, `companies`.`company_name`, `contacts`.`contact_name` ASC
And I've tried moving the 2 LEFT JOIN `countries` to just before the WHERE as well but I keep getting the same error:
Not unique table/alias: 'countries'
I really think there's some syntax I need to use here that I don't know about :(

Any more hints VladSun? ;)

Cheers, B

Re: MySQL, get joined records separated by commas

Posted: Mon Dec 01, 2008 3:19 am
by VladSun
First, to join a table more than once use alias name.
[sql]LEFT JOIN `countries` ON `contacts`.`geo`=`countries`.`geo` LEFT JOIN `countries` AS `company_countries` ON `companies`.`geo`=`company_countries`.`geo`[/sql]

Second, maybe you don't need these two joins:
[sql]LEFT JOIN `countries` ON `contacts`.`geo`=`countries`.`geo` OR `companies`.`geo`=`countries`.`geo`[/sql]

Re: MySQL, get joined records separated by commas

Posted: Mon Dec 01, 2008 6:35 am
by batfastad
Hi VladSun
That's awesome. I would not have worked that out.
I think I need the 2 separate joins though, because companies.geo and countries.geo are not necessarily the same value, and I need to return both.

However I'm still having an error.
#1054 - Unknown column 'companies.geo' in 'on clause'
Does that mean I need to have companies in my FROM clause for it to be recognised by ON?

Code: Select all

SELECT `companies`.`address1`, `companies`.`address2`, `companies`.`address3`, `companies`.`company_name`, `companies`.`county_state`, `companies`.`postcode_zip`, `companies`.`town_city`, `contacts`.`address1_contact`, `contacts`.`address2_contact`, `contacts`.`address3_contact`, `contacts`.`contact_name`, `contacts`.`county_state_contact`, `contacts`.`mail_contact`, `contacts`.`postcode_zip_contact`, `contacts`.`reg_stamp`, `contacts`.`town_city_contact`, `companies_countries`.`country`, `contacts_countries`.`country`, GROUP_CONCAT(`tags`.`name` ORDER BY `tags`.`name` ASC SEPARATOR ',') AS `classifications` FROM `contacts` LEFT JOIN `countries` AS `contacts_countries` ON `contacts`.`geo_contact`=`contacts_countries`.`iso2` LEFT JOIN `countries` AS `companies_countries` ON `companies`.`geo`=`companies_countries`.`iso2` LEFT JOIN `companies` USING(`company_id`) LEFT JOIN `tags_data` ON `companies`.`company_id`=`tags_data`.`company_id` LEFT JOIN `tags` ON `tags_data`.`tag_id`=`tags`.`tag_id` AND `tags`.`category`='OE FRANCHISE' WHERE `contacts`.`mag_amd`='FREE' GROUP BY `contacts`.`contact_id` ORDER BY `companies`.`geo`, `companies`.`company_name`, `contacts`.`contact_name` ASC
Thanks, B

Re: MySQL, get joined records separated by commas

Posted: Mon Dec 01, 2008 6:59 am
by VladSun
You must have company table already joined before you can use fields from it.

Take a look at my last post in this thread: viewtopic.php?f=2&t=90597

So, you have to change the order of your joins.

PS: You don't need an alias for the first join of countries table, although it's more readable your way ;) .

Re: MySQL, get joined records separated by commas

Posted: Tue Dec 02, 2008 8:59 am
by batfastad
Yes that's awesome!!! :lol:
I didn't know you could use aliases on a join name, but now I do! I think I should be able to work out most queries now I've got all these examples.

Thanks for all your help on this VladSun, I really appreciate it :D

Re: MySQL, get joined records separated by commas

Posted: Mon Dec 08, 2008 5:30 am
by VladSun
VladSun wrote:
batfastad wrote:I'm running the WHERE on contacts.mag_amd=FREE, rather than subscribed=1, because I need to test with returning up to 20,000 records. subscribed=1 only gives me 777 but I will be needing this query on larger datasets.
MySQL does not support partial indices.
Too bad for MySQL ;)
For large DBs you should avoid using char type fields in every clause, except SELECT.
http://dev.mysql.com/tech-resources/art ... oning.html :)