I'm going re-post this query. Roger Backlund had been attempting to help me but I've either stumped him or he's busy having a life :).... Besides, my first posting had several problems, since I was working from memory. Finally, in the process of preparing this query, I found a small change that makes it work, but I would like to know why that change is necessary. Also, I don't want to have to use unique column names throughout the database, just to make mySQL queries work correctly.
What I did that finally made the query work correctly was to modify the column names in the two tables that had the same column name. This is the syntax that works: SELECT Languages.LName, Competencies.CName, Researchers2Languages.Researcher FROM (Languages INNER JOIN Researchers2Languages ON Languages.Language = Researchers2Languages.Language) INNER JOIN Competencies ON Researchers2Languages.Competence = Competencies.Competence WHERE Researchers2Languages.Researcher = 39; results in : >LName CName Researcher >English Native speaker 39 >German Can read 39 But with: SELECT Languages.Name, Competencies.Name, Researchers2Languages.Researcher FROM (Languages INNER JOIN Researchers2Languages ON Languages.Language = Researchers2Languages.Language) INNER JOIN Competencies ON Researchers2Languages.Competence = Competencies.Competence WHERE Researchers2Languages.Researcher = 39; (The difference is in the two column names [.LName vs .Name, .CName vs .Name] in the first lines.) results in: >Name Name Researcher >Native speaker Native&.nbsp.speaker 39 >Can read Can&.nbsp.read 39 I have no problem doing that if I don't include the Competencies table. But as soon as I add the third table, the results get weird, primarily putting the Competency.Name in the column assigned to Language.Name and to the column assigned to Competency.Name, but converting any spaces into & nbsp ; Can anyone explain why mysql produced this weird result, even though the columns with the same name were qualified with the table names? TIA, James Birkholz Admin of Posen-L mailing list & website (Here is some more info, if it helps:) This is for a genealogy website. I have 3 tables involved: a Researcher2Languages linking table that contains 0 or more records for every record in a Researcher table (which is not directly involved in this query). Researcher2Languages contains foreign keys to the two other tables; Languages, which is a lookup list of 200+ modern spoken languages (autonumber primary key); and Competencies, a lookup list of how well someone knows the language (currently three possible values, 1-3, also autonumber primary key). Environment: remote connection to a Linux/Apache/MySQL/php server administered by someone else. Using phpMyAdmin to test queries. I presume that if it works there, I can then write some php code to use it. I've tried creating a functioning mirror of the database and query in Access97 (which I'm fairly familiar with) and trying to use the sql query in phpMyAdmin, but it won't run without modifying the syntax > >Database PosenL running on localhost >Showing records 0 - 2 (2 total) > >SQL-query : [Edit] >SELECT L.Name, C.Name, R.Researcher FROM Researchers2Languages R LEFT JOIN >Languages L ON L.Language = R.Language LEFT JOIN Competencies C ON >C.Competence = R.Competence WHERE R.Researcher = 39 LIMIT 0, 30 > >Name Name Researcher >Native speaker Native&.nbsp.speaker 39 >Can read Can&.nbsp.read 39 > >Note that I'm still getting C table values in the B value column, and still >getting the " " on the B column values.... > >Here are the dumps: > >desc Researchers2Languages >Field Type Null Key Default Extra >Researcher smallint(6) MUL 0 >Language smallint(6) 0 >Competence tinyint(4) YES NULL > >desc Languages >Field Type Null Key Default Extra >Language smallint(6) PRI NULL auto_increment >Name varchar(25) MUL >ShortName char(3) >Rank tinyint(4) MUL 0 >Western char(1) > >desc Competencies >Field Type Null Key Default Extra >Competence tinyint(4) PRI NULL auto_increment >Name varchar(15) > >Researcher Language Competence >39 1 1 >39 2 3 > > Language Name ShortName Rank Western > 1 English ENG 1 Y > 2 German GER 2 Y > 3 Polish POL 3 Y > 4 French FRE 4 Y >(remaining 200+ records left out) > >Competence Name >1 Native speaker >2 Can translate >3 Can read > >Hope you can spot something... >Thanks! --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
