What is the right combination of joins and select statements to get this information from a mysql database?
February 25, 2008 10:39 AM Subscribe
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?
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.
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
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:
posted by nicwolff at 11:06 AM on February 25, 2008
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
posted by tomorama at 3:47 PM on February 25, 2008
This thread is closed to new comments.
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