Hi Deviad, NATURAL JOIN uses all column names that are the same between both tables as conditions.
select * from rappresentanti NATURAL JOIN clienti; is the same as: select * from rappresentanti r JOIN client c ON r.cognome=c.cognome AND r.nome=c.nome AND r.vita=c.vita AND r.citta=c.citta AND r.prov=c.prov AND r.cap=c.cap AND r.CodRappr=c.CodRappr; Regards, Gavin Towey -----Original Message----- From: Deviad [mailto:dev...@msn.com] Sent: Monday, August 24, 2009 6:27 PM To: mysql@lists.mysql.com Subject: Re: Natural Join Issue: column names are equal but doesn't work anyways Hi again, since I'm not someone who gives up easily, I have restyled that old code (actually is from an example back of my teacher into 2002, I saw that she changed a bit her way to code this stuff), I restyled the code just to be sure it isn't some parsing issue or whatever. http://pastebin.com/f50d77dcf On that database, this query works: select CodCliente, Cognome, Nome from Ordini NATURAL JOIN Clienti where Data='2002-09-05'; whereas this one does not: select * from rappresentanti NATURAL JOIN clienti; I pasted the database in there. Deviad ha scritto: > Hello, > I have been training for a test on Database Design and MySQL. > The following is inside a file we have to run before starting to code > what the excercises require us to. > Call the file as you wish and run it if it helps to understand the > reason behind my problem. > --------------------------- > DROP DATABASE IF EXISTS premiere; > > CREATE DATABASE premiere; > > USE premiere; > > create table if not exists Articoli( > NroArt char(4) primary key, > descrizione char(20), > giacenza int, > categoria char (2), > PrezzoUnitario decimal(8,2) > ) TYPE=INNODB; > > create table if not exists Rappresentanti( > CodRappr char(2) primary key, > cognome char(10), > nome char(8), > via char (15), > citta char(15), > prov char(2), > cap char (5), > TotProvv decimal(8,2), > PerProvv decimal(8,2) > ) TYPE=INNODB; > > > create table if not exists clienti( > CodCliente char(3) primary key, > cognome char(10), > nome char(8), > via char (15), > citta char(15), > prov char(2), > cap char (5), > saldo decimal(8,2), > fido decimal(8,2), > CodRappr char(2) not null references > Rappresentanti(CodRappr) > ) TYPE=INNODB; > > > create table if not exists Ordini(NroOrdine char(6) primary key, > data date, > CodCliente char(3) not null > references Clienti(CodClienti) > ) TYPE=INNODB; > > > insert into articoli > values ('AX12','ferro da stiro',104,'cs',24.95); > insert into articoli > values ('AZ52','freccette',20,'sp',12.95); > insert into articoli > values ('BA74','pallone',40,'sp',29.95); > insert into articoli > values ('BH22','tritatutto',05,'cs',24.95); > insert into articoli > values ('BT04','forno',11,'el',149.49); > insert into articoli > values ('BZ66','lavatrice',52,'el',399.99); > insert into articoli > values ('CA14','setaccio',78,'cs',39.99); > insert into articoli > values ('CB03','bicicletta',44,'sp',299.99); > insert into articoli > values ('CX11','frullino',142,'cs',22.95); > insert into articoli > values ('CZ81','tavola pesi',68,'sp',349.95); > > > insert into Rappresentanti > values('03','Jones','Mary','123 Main','Grant','MI','49219',215,5); > insert into Rappresentanti > values('06','Smith','William','102 > Raymond','Ada','MI','49441',49412.5,7); > insert into Rappresentanti > values('12','Diaz','Miguel','419 Harper','Lansing','MI','49224',2150,5); > > > insert into clienti > values > ('124','Adams','Sally','481Oak','Lansing','MI','49224',818.75,1000,'03'); > insert into clienti > values > ('256','Samuel','Ann','215Pete','Grant','MI','49219',21.5,1500,'06'); > insert into clienti > values > ('311','Charles','Don','48College','Ira','MI','49034',825.75,1000,'12'); > insert into clienti > values > ('315','Daniels','Tom','914Charry','Kent','MI','48391',770.75,750,'06'); > insert into clienti > values > ('405','Williams','Al','519Watson','Grant','MI','49219',402.75,1500,'12'); > insert into clienti > values > ('412','Adams','Sally','16Elm','Lansing','MI','49224',1817.5,2000,'03'); > insert into clienti > values > ('522','Nelson','Mary','108Pine','Ada','MI','49441',98.75,1500,'12'); > insert into clienti > values > ('567','Dinh','Tran','808Ridge','Harper','MI','48421',402.4,750,'06'); > insert into clienti > values > ('587','Galvez','Mara','512Pine','Ada','MI','49441',114.6,1000,'06'); > insert into clienti > values > ('622','Martin','Dan','419Chip','Grant','MI','49219',1045.75,1000,'03'); > > > insert into ordini > values('12489','2002-09-02','124'); > insert into ordini > values('12491','2002-09-02','311'); > insert into ordini > values('12494','2002-09-04','315'); > insert into ordini > values('12495','2002-09-04','256'); > insert into ordini > values('12498','2002-09-05','522'); > insert into ordini > values('12500','2002-09-05','124'); > insert into ordini > values('12504','2002-09-05','522'); > ----------------------------------------------------- > > Now, this is what is giving me an issue: > ------------------------------------------------------- > use premiere; > > > select * > from clienti as c join rappresentanti as r on c.codrappr = r.codrappr > where r.codrappr='03'; > > select * > from rappresentanti NATURAL JOIN clienti; > ------------------------------------------------------ > > For some weird reason the natural join returns an empty set, but there > is a column, just the one I need whose name is the same > in both relations which is Codrappr. > Why doesn't it work as it's supposed to? > > As you can see they are the same: > create table if not exists Rappresentanti( > CodRappr char(2) primary key, > cognome char(10), > nome char(8), > via char (15), > citta char(15), > prov char(2), > cap char (5), > TotProvv decimal(8,2), > PerProvv decimal(8,2) > ) TYPE=INNODB; > > > create table if not exists clienti( > CodCliente char(3) primary key, > cognome char(10), > nome char(8), > via char (15), > citta char(15), > prov char(2), > cap char (5), > saldo decimal(8,2), > fido decimal(8,2), > CodRappr char(2) not null references > Rappresentanti(CodRappr) > ) TYPE=INNODB; > > > > Thank you very much in advance. > I hope someone will solve this thing. > > > > -- Reclaim Your Inbox! http://www.mozilla.org/products/thunderbird -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=gto...@ffn.com The information contained in this transmission may contain privileged and confidential information. It is intended only for the use of the person(s) named above. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
