On Tue, Aug 25, 2009 at 6:55 AM, Deviad <dev...@msn.com> wrote:
> 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.
>
>
I think the result of NATURAL JOIN is correct.
For refference about JOIN and especially NATURAL JOIN, you can see
http://dev.mysql.com/doc/refman/5.1/en/join.html
--
Muhammad Subair
