For posterity (ie the archives) there's a comma missing in the FROM line: FROM contact AS cn, codes AS cd1, codes AS cd2
Roddie Grant on 25/5/04 8:25 am, Greg Willits at [EMAIL PROTECTED] wrote: > 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]
