MySQL, get joined records separated by commas

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

User avatar
batfastad
Forum Contributor
Posts: 433
Joined: Tue Mar 30, 2004 4:24 am
Location: London, UK

Re: MySQL, get joined records separated by commas

Post 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
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: MySQL, get joined records separated by commas

Post 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]
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
batfastad
Forum Contributor
Posts: 433
Joined: Tue Mar 30, 2004 4:24 am
Location: London, UK

Re: MySQL, get joined records separated by commas

Post 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
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: MySQL, get joined records separated by commas

Post 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 ;) .
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
batfastad
Forum Contributor
Posts: 433
Joined: Tue Mar 30, 2004 4:24 am
Location: London, UK

Re: MySQL, get joined records separated by commas

Post 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
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: MySQL, get joined records separated by commas

Post 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 :)
There are 10 types of people in this world, those who understand binary and those who don't
Post Reply