I'm not (yet) using sub-queries since the old version of MySQL were
unable to handle them, then I was using 'join'.
I wish to know if it's possibile to do all what I did with 'join'
with subqueries.
and which one is faster/better to use?
for example it'd be possibile to 'translate' that using subselect?
SELECT Esercente.Insegna, Terminale.TermId, Abilitazione.CodCarta,
Circuito.Nome, Abilitazione.Data, Circuito.Tel
FROM Esercente
INNER JOIN Terminale ON Esercente.CodSia = Terminale.CodSia
LEFT JOIN Abilitazione ON Terminale.TermId=Abilitazione.TermId
LEFT JOIN Circuito ON Abilitazione.CodCarta=Circuito.CodCarta
my db is that (and on attachment there is the pdf with the logical
schema)
CREATE DATABASE IF NOT EXISTS CentroServizi;
USE CentroServizi;
CREATE TABLE IF NOT EXISTS Esercente
(
CodSia MEDIUMINT UNSIGNED NOT NULL, #Il CodSia e' un numero
positivo a 7
# cifre;
# MEDIUMINT UNSIGNED va da 0
a 16777215
Citta VARCHAR(20) NOT NULL,
Insegna VARCHAR(50),
PRIMARY KEY (CodSia)
)
TYPE = InnoDB
CREATE TABLE IF NOT EXISTS Terminale
(
TermId MEDIUMINT UNSIGNED NOT NULL, # il TermId e' numero positivo
a 6 cifre
# MEDIUMINT UNSIGNED va da 0
a 16777215
CodSia MEDIUMINT UNSIGNED NOT NULL,
Modello VARCHAR(20),
PRIMARY KEY (TermId),
INDEX CodSia (CodSia),
FOREIGN KEY (CodSia) REFERENCES Esercente(CodSia)
ON UPDATE CASCADE
ON DELETE CASCADE
)
TYPE=INNODB;
CREATE TABLE IF NOT EXISTS Circuito
(
CodCarta TINYINT UNSIGNED NOT NULL, # il CodCarta e' un numero
positivo a 2
# cifre
# TINYINT UNSIGNED va da 0 a 255
Nome VARCHAR(20) NOT NULL,
Tel VARCHAR(50),
PRIMARY KEY (CodCarta)
)
TYPE=INNODB;
CREATE TABLE IF NOT EXISTS Abilitazione
(
TermId MEDIUMINT UNSIGNED NOT NULL,
CodCarta TINYINT UNSIGNED NOT NULL,
Data DATE NOT NULL,
PRIMARY KEY (TermId, CodCarta),
INDEX TermId (TermId),
INDEX CodCarta (CodCarta),
FOREIGN KEY (TermId) REFERENCES Terminale(TermId)
ON UPDATE CASCADE
ON DELETE CASCADE,
FOREIGN KEY (CodCarta) REFERENCES Circuito(CodCarta)
ON UPDATE CASCADE
ON DELETE CASCADE
)
TYPE=INNODB;
CREATE TABLE IF NOT EXISTS ChiaInt
(
CallId INT UNSIGNED NOT NULL, # Si potranno gestire fino a
4294967295
# chiamate
Operatore VARCHAR(20) NOT NULL,
Chiamante MEDIUMINT UNSIGNED NOT NULL , # e' il CodSia
Inizio DATETIME NOT NULL,
Fine DATETIME NULL,
Manutentore VARCHAR(20),
Motivo TEXT, # Stringa lunga al max 65KB
PRIMARY KEY (CallId),
INDEX Chiamante (Chiamante),
FOREIGN KEY (Chiamante) REFERENCES Esercente(CodSia)
ON UPDATE CASCADE
ON DELETE CASCADE
)
TYPE=INNODB;
thank you in advance
MAS!
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
