Having trouble with mysql relationships?
I need two tables, one showing one to many relationships using three tables, and another table showing many-to-many relationships. I was able to do the first 2 with queries involving only one table, and one for many involving 2 tables.
One table query:
SELECT LastName, FirstName, City
FROM Customer
WHERE City = 'Lutherville-Timonium'
One for many, including 2 tables:
SELECT b.BrandName, m.ModelName
FROM Brand b, Model m
WHERE b.BrandID = m.BrandID
Here is my code:
DROP DATABASE IF EXISTS FinalProject;
CREATE DATABASE IF NOT EXISTS FinalProject;
Use FinalProject;
CREATE TABLE IF NOT EXISTS Brand
(
BrandID INT,
BrandName VARCHAR(45),
PRIMARY KEY(BrandID)
);
CREATE TABLE IF NOT EXISTS Model
(
ModelName VARCHAR(45),
BrandID INT,
PRIMARY KEY(ModelName),
FOREIGN KEY(BrandID) REFERENCES Brand(BrandID)
);
CREATE TABLE IF NOT EXISTS Platform
(
PlatformID INT,
Platform VARCHAR(45),
PRIMARY KEY(PlatformID)
);
CREATE TABLE IF NOT EXISTS Computer
(
ComputerID INT,
ModelName VARCHAR(45),
PlatformID INT,
Processor VARCHAR(45),
Memory VARCHAR(45),
HardDrive VARCHAR(45),
OperatingSystem VARCHAR(45),
PRIMARY KEY(ComputerID),
FOREIGN KEY(ModelName) REFERENCES Model(ModelName),
FOREIGN KEY(PlatformID) REFERENCES Platform(PlatformID)
);
CREATE TABLE IF NOT EXISTS Customer
(
CustomerID INT,
LastName VARCHAR(45),
FirstName VARCHAR(45),
Address VARCHAR(45),
Zip INT,
City VARCHAR(45),
State VARCHAR(2),
PhoneNumber INT,
Email VARCHAR(45),
PRIMARY KEY(CustomerID)
);
CREATE TABLE IF NOT EXISTS Employee
(
EmployeeID INT,
Position VARCHAR(45),
LastName VARCHAR(45),
FirstName VARCHAR(45),
Address VARCHAR(45),
Zip INT,
City VARCHAR(45),
State VARCHAR(2),
PhoneNumber INT,
Email VARCHAR(45),
PRIMARY KEY(EmployeeID)
);
CREATE TABLE IF NOT EXISTS Invoice
(
InvoiceID INT,
CustomerID INT,
Total DECIMAL(19,4),
EmployeeID INT,
PRIMARY KEY(InvoiceID),
FOREIGN KEY(CustomerID) REFERENCES Customer(CustomerID),
FOREIGN KEY(EmployeeID) REFERENCES Employee(EmployeeID)
);
CREATE TABLE IF NOT EXISTS InvoiceItem
(
InvoiceItemID INT,
InvoiceID INT,
ComputerID INT,
PRIMARY KEY(InvoiceItemID),
FOREIGN KEY(InvoiceID) REFERENCES Invoice(InvoiceID),
FOREIGN KEY(ComputerID) REFERENCES Computer(ComputerID)
);
INSERT INTO Brand
(BrandID, BrandName)
VALUES
(101, 'Toshiba'),
(102, 'ASUS'),
(103, 'Dell'),
(104, 'Samsung'),
(105, 'MSI'),
(106, 'Apple');
INSERT INTO Model
(ModelName, BrandID)
VALUES
('Satellite L50D-BBT2N22', 101),
('Transformer Book T100TAF', 102),
('Inspiron 3000', 103),
('ATIV One 7 Curved', 104),
('GE60 2PE APACHE PRO', 105),
('Summer 2014 13 in. MacBook Pro', 106),
('Wind Box DC111', 105),
('Alienware 17', 103),
('ATIV Book 9 Plus', 104);
INSERT INTO Platform
(PlatformID, Platform)
VALUES
(001, 'Laptop'),
(002, 'Desktop');
INSERT INTO Computer
( ComputerID, ModelName, Processor, Memory, HardDrive, OperatingSystem, PlatformID)
VALUES
(178045, 'Satellite L50D-BBT2N22', 'AMD Quad-Core A4-6210', '4GB', '1TB', 'Windows 8.1', 001),
(178046, 'Transformer Book T100TAF', 'Intel Bay Trail-T Quad Core Z3735 1.33 GHz', '2GB', '32GB', 'Windows 8.1', 001),
(178047, 'Inspiron 3000', 'Intel Core i5-4460', '8GB', '1TB', 'Windows 8.1', 002),
(178048, 'ATIV One 7 Curved', 'Intel Core i5 Processor 5200U', '8GB', '1TB', 'Windows 8.1', 002),
(178049, 'GE60 2PE APACHE PRO', 'Intel Core i7 Processor', '16GB', '1TB', 'Windows 8.1', 001),
(178050, 'Summer 2014 13 in. MacBook Pro', '2.5GHz dual-core Intel Core i5', '4GB', '500GB', 'OS X Yosemite', 001),
(178051, 'Wind Box DC111', 'Intel Celeron Dual Core', '4GB', ' 500GB', 'Windows 8.1', 002),
(178052, 'Alienware 17', 'Intel Core i7', '8GB', '1TB', 'WIndows 8.1', 001),
(178053, 'ATIV Book 9 Plus', 'Intel Core i5', '8GB', '128GB', 'Windows 8.1', 001);
INSERT INTO Customer
(CustomerID, LastName, FirstName, Address, Zip, City, State, PhoneNumber, Email)
VALUES
(14670, 'Franks', 'Robert', '2905 North Ave.', 21218, 'Baltimore', 'MD', 443-875-9090, 'r.franks@gmail.com'),
(14671, 'Smith', 'Anthony', '28 Rhodes Pl.', 21093, 'Lutherville-Timonium', 'MD', 410-252-6542, 'asmith@me.com'),
(14672, 'Anderson', 'Mary', '1784 Cranbrook Dr.', 21093, 'Lutherville-Timonium', 'MD', 410-687-8235, 'm.anderson@comcast.net'),
(14673, 'Keith', 'Toby', '987 Rodeo Dr.', 21093, 'Lutherville-Timonium', 'MD', 443-267-0900, 'not_that_toby.keith@gmail.com'),
(14674, 'Karwacki', 'Ryan', '16200 Yeoho Rd', 21152, 'Hereford', 'MD', 410-350-4456, 'r.karwacki44@gmail.com'),
(14675, 'Yancey', 'Marcus', '165 Twilight Ct.', 21218, 'Baltimore', 'MD', 443-908-9087, 'm.yancey35@aol.com');
INSERT INTO Employee
(EmployeeID, Position, LastName, FirstName, Address, Zip, City, State, PhoneNumber, Email)
VALUES
(100001, 'CEO', 'Brocato', 'Christopher', '26 Rhodes Pl.', 21093, 'Lutherville-Timonium', 'MD', 410-812-0548, 'c.brocat0@prestigeww.com'),
(100893, 'Location Manager', 'White', 'Walter', '687 Winning Dr.', 21117, 'Owings Mills', 'MD', 410-674-8890, 'w.white@prestigeww.com'),
(100894, 'Computer Engineer', 'Pinkman', 'Jesse', '15 Pot Spring Cr.', 21093, 'Lutherville-Timonium', 'MD', 443-897-5467, 'j.pinkman@prestigeww.com'),
(100895, 'Computer Engineer', 'Fring', 'Gustavo', '8796 Westmister Br.', 21117, 'Owings Mills', 'MD', 443-098-1111, 'g.fring@prestigeww.com'),
(100896, 'Software Specialist', 'Boetticher', 'Gale', '7845 Sunny Ln.', 21093, 'Lutherville-Timonium', 'MD', 443-896-5674, 'g.boetticher@prestigeww.com'),
(100087, 'Lawyer', 'Goodman', 'Saul', '7823 Goodman Ln.', 21093, 'Lutherville-Timonium', 'MD', 410-657-8900, 's.goodman@prestigeww.com');
INSERT INTO Invoice
(InvoiceID, Total, CustomerID, EmployeeID)
VALUES
(237871, 115.99, 14670, 100893),
(237872, 87.89, 14671, 100894),
(237873, 476.95, 14672, 100895),
(237874, 314.95, 14673, 100896),
(237875, 45.96, 14674, 100001),
(237876, 79.84, 14675, 100087);
INSERT INTO InvoiceItem
(InvoiceItemID, InvoiceID, ComputerID)
VALUES
(1008, 237871, 178045),
(1009, 237872, 178046),
(1010, 237873, 178047),
(1011, 237874, 178048),
(1012, 237875, 178049),
(1013, 237876, 178050);
Any help would be greatly appreciated Thanks.
+3
source to share
1 answer
This should do the job:
SELECT *
FROM computer c
LEFT OUTER JOIN model m ON m.ModelName = c.ModelName
LEFT OUTER JOIN brand b ON b.BrandID = m.BrandID
You can change the returned fields to suit your requirements. Good luck with your project.
=== Update - To order it as you requested:
SELECT *
FROM brand b
LEFT OUTER JOIN model m ON m.BrandID = b.BrandID
LEFT OUTER JOIN computer c ON c.ModelName = m.ModelName
=== Update # 2 - include a new "platform" table
SELECT *
FROM brand b
LEFT OUTER JOIN model m ON m.BrandID = b.BrandID
LEFT OUTER JOIN computer c ON c.ModelName = m.ModelName
LEFT OUTER JOIN platform p ON p.platformID = c.PlatformID
=== Update # 3 - The above covers 1-many relationships for 2,3 and 4 tables. To add a many-to-many relationship, you probably want to look at a computer to link an invoice, since a computer can be in multiple invoices, and an invoice can (in real life) contain more than one computer. The originally proposed data structure does not allow this. Adding a table for the InvoiceItem would achieve this.
the relationship between computer and invoice will be many for many [Computer (many) - (many) invoices] This is achieved by adding a table of links between them:
Computer (1)-(Many) InvoiceItem (many)-(1) Invoice
=== Update # 4 To do this, remove the computerID from the invoice table and add a new InvoiceItem table with the following fields - InvoiceItemID, InvoiceNumber, ComputerID. The query to retrieve account items from the database will look like this:
SELECT *
FROM Invoice i
LEFT OUTER JOIN InvoiceItem ii ON ii.InvoiceNumber = i.InvoiceNumber
LEFT OUTER JOIN Computer c ON c.computerID = ii.computerID
You can extend this to include other computer related tables as well:
SELECT *
FROM Invoice i
LEFT OUTER JOIN InvoiceItem ii ON ii.InvoiceNumber = i.InvoiceNumber
LEFT OUTER JOIN Computer c ON c.computerID = ii.computerID
LEFT OUTER JOIN Model m ON m.ModelName = c.ModelName
LEFT OUTER JOIN Brand b ON b.BrandID = m.BrandID
LEFT OUTER JOIN platform p ON p.platformID = c.PlatformID
If I was really picky I would ditch the name references and just use IDs, if the model name changes a little the queries won't work anymore. Using pure identifiers prevents this from happening.
+2
source to share