Re: SELECTing to Substitute Text for Refc Codes

[Greg Willits]

Got an offline answer, so I'm all set. Gotta love SQL.

for posterity...

Try:
SELECT cn.name, cd1.desc ,cd2.desc
FROM contact AS cn, codes AS cd1 codes AS cd2
WHERE cn.city = cd1.code AND cn.state = cd2.code

given:
CREATE TABLE `codes` (
  `id` int(11) NOT NULL auto_increment,
  `code` varchar(16) default NULL,
  `desc` varchar(64) default NULL,
  PRIMARY KEY  (`id`)
) TYPE=MyISAM;

CREATE TABLE `contact` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(32) default NULL,
  `city` varchar(16) default NULL,
  `state` varchar(16) default NULL,
  PRIMARY KEY  (`id`)
) TYPE=MyISAM;

-- gw

On May 24, 2004, at 5:12 PM, Greg Willits wrote:

Struggling with a query structure new to me...

Most of my apps are not very complex at all (simple joins, few functions), but I've worked through a few "tricky" queries. However, this one is stumping me on how to even begin. I can't even decide what kind of query it is. As you can I wasn't even sure what to call it :-(

I'll distill it to a structural example. Two tables: first table (contacts) has basic contact stuff (Name, city, State), second table (syscodes) is a arbitrary collection of "codes" and "descriptions" (code, desc).

The contacts table uses codes from the sysycodes table instead of actual text for stuff like city and state. So, the contacts table might look like:

        Will Doolittle, 1543, 425
        Robin Banks, 1800, 12897

And, the syscodes table is

        425, California
        1543, Anaheim
        1800, Topeka
        12897, Kansas

In my realm this is a sadistic level of normalization, but I suppose it is pretty common and must be useful at some scale (I'm getting data from a large U.S. county). Either I'm thinking too hard, or really don't get it, but I don't know how to pull those together to get:

        Will Doolittle, Anaheim, California
        Robin Banks, Topeka, Kansas

So starting with the basics:

        SELECT cntcName, cntcCity, cntcState
        FROM contacts, syscodes
        WHERE
                cntcID="x"
                AND cntcCity=code
                AND cntcState=code

(which I know is wrong)

But how to connect which row from syscodes goes to which cntc field? The only way I can seem to visualize this is as a bunch of substitutions to alias with AS in the select phrase, but I don't see how.

Unfortunately, this has to be compatible with 3.23.54. (when 4.1 hits beta then I will likely switch to it for this particular app as I need sub-selects for another task).

All clues welcomed.

-- greg willits

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]