What is the right combination of joins and select statements to get this information from a mysql database?

February 25, 2008 10:39 AM

MYSQL newbie needs some help with a crazy query! (Joins, foreign keys, and bears "oh my!")

Hallo, all. So I'm just starting to get the hang on writing mysql queries with JOINS, but I'm having the most difficult time with what I believe is a rather simple query. I'm wondering if someone here can help me out. I've got a rather complicated database of customers with a whole bunch of related tables and foreign keys. (MYSQL structure is pasted below)

Basically, I need to write a query that will a output all customers with the appropriate labels replacing the ugly "foreign keys" that are in there. I've been screwing around with joins for the better part of two days but I just can't wrap my mind around what should be (I think) a very simple query. Something akin to:

first_name | last_name | email | gender | customer_types.description | city | states.abbrev | zip | countries.name | ethnic_backgrounds.name | interest.interest_title(1) | interest.interest_title(2) | interest.interest_title(2) | inquiry_sources.sourcename

Can any query wizards out there lend this newbie a hand?

CREATE TABLE `customers` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `first_name` varchar(255) NOT NULL default '',
  `last_name` varchar(255) NOT NULL default '',
  `email` varchar(255) NOT NULL default '',
  `gender` varchar(10) NOT NULL default '',
  `customer_type_id` int(10) unsigned NOT NULL default '0',
  `city` varchar(255) NOT NULL default '',
  `state_id` int(10) unsigned NOT NULL default '0',
  `zip` varchar(255) NOT NULL default '',
  `country_id` int(10) NOT NULL default '0',
  `ethnic_background_id` int(10) unsigned NOT NULL default '0',
  `interest1_id` int(10) unsigned NOT NULL default '0',
  `interest2_id` int(10) unsigned NOT NULL default '0',
  `interest3_id` int(10) unsigned NOT NULL default '0',
  `inquiry_source_id` int(10) unsigned NOT NULL default '0'
  PRIMARY KEY  (`id`)
)

CREATE TABLE `customer_types` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `description` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`id`)
)

CREATE TABLE `states` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `name` varchar(255) NOT NULL default '',
  `abbrev` varchar(4) NOT NULL default '',
  PRIMARY KEY  (`id`)
) TYPE=MyISAM AUTO_INCREMENT=67 ;

CREATE TABLE `countries` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(100) NOT NULL default '',
  PRIMARY KEY  (`id`)
)

CREATE TABLE `ethnic_backgrounds` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `name` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`id`)
) 

TABLE `interests` (
  `Record_id` int(11) NOT NULL auto_increment,
  `interest_title` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`Record_id`)
)

CREATE TABLE `inquiry_sources` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `sourcename` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`id`)
) 

Thanks in advance for any help.

posted by lucidreamstate to computers & internet (4 comments total)

SELECT customers.foo, customers.bar, states.name, inquiry_sources.sourcename ...
FROM customers
LEFT JOIN states ON (customers.state_id=states.id)
LEFT JOIN inquiry_sources ON (customers.inquiry_source_id=inquiry_source.id)
...
;
posted by cmiller at 10:42 AM on February 25, 2008

Oh, you may not need parens above. Sorry.

And, "interests" has a trick. Since you're joining to another table several times, you need to give it an alias each time so there's no ambiguity.


int1.interest_title, int2.interest_title

LEFT JOIN interests AS int1 ON customers.interest1=int1.Record_id.
LEFT JOIN interests AS int2 ON customers.interest2=int2.Record_id.
posted by cmiller at 10:46 AM on February 25, 2008

The foreign keys are all NOT NULL so the outer joins don't have to be left (but for referential integrity they should have REFERENCES constraints); you can rename columns in the output table with AS; and as long as a column name is unique in the join you don't have to qualify it with the table name. So:

select first_name, last_name, e_mail, gender, customer_types.description as customer_type, 
  city, states.name as state, zip, countries.name as country, ethnic_backgrounds.name as ethnic_background, 
  i1.interest_title as interest1, i2.interest_title as interest2, i3.interest_title as interest3, 
  source_name as inquiry_source 
from customers 
  join customer_types on customer_type_id = customer_types.id 
  join states on state_id = states.id 
  join countries on country_id = countries.id 
  join ethnic_backgrounds on ethnic_background_id = ethnic_backgrounds.id 
  join interests i1 on interest1_id = i1.id 
  join interests i2 on interest2_id = i2.id 
  join interests i3 on interest3_id = i3.id 
  join inquiry_sources on inquiry_source_id = inquiry_sources.id

posted by nicwolff at 11:06 AM on February 25, 2008

nicwolff's got it. Since you're joining one table onto another multiple times, you can use multiple joins with a different alias each time.
posted by tomorama at 3:47 PM on February 25, 2008