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=arch...@jab.org
