I’m having trouble creating my own SQL databases.
I’m using MySQL with InnoDB
Here’s how I create my Tables (all the data is required):
CREATE TABLE Issuers ( id int NOT NULL AUTO_INCREMENT UNIQUE, name varchar(255) NOT NULL, img varchar(255) NOT NULL, url varchar(255) NOT NULL, PRIMARY KEY (id) ); CREATE TABLE Certificates ( id int NOT NULL AUTO_INCREMENT UNIQUE, issuer_id int NOT NULL, name varchar(255) NOT NULL, date_issued DATE NOT NULL, img varchar(255) NOT NULL, ver_url varchar(255) NOT NULL, pdf varchar(255) NOT NULL, PRIMARY KEY (id), CONSTRAINT FK_issuer_id FOREIGN KEY (id) REFERENCES Issuers(id) );
As you can see in the Table “Certificates” I’m using Issuers(id) as a Foreign Key because I want to have a different Table for Issuers and I want my options limited to what I have in my other table
Here’s some data as an example:
INSERT INTO Issuers VALUES (1, "Coursera", "imgs/certificates/issuer/coursera.png", "https://www.coursera.org"), (2, "Udemy", "imgs/certificates/issuer/coursera.png", "https://www.udemy.com"), (3, "SoloLearn", "imgs/certificates/issuer/sololearn.png", "https://www.sololearn.com"); INSERT INTO Certificates VALUES ( 1, 1, "IT Security: Defense against the digital dark arts", "2019-11-10", "imgs/certificates/it_security_support.png", "link_url", "docs/certificates/it_support.pdf" );
As you can see my data is inserted in the right order (at least I hope so.)
and I’m trying to combine my tables with Select but nothing works from what I have tried.
The final result from a Select query is this:
id = 1 issuer_id = 1 issuer_name = Coursera name = "IT Security Defense..." date_issued = 2019-11-10 img = imgs/certificates/it_security_support.png ver_url = link_url pdf = docs/certificates/it_support.pdf
Here’s some queries that I have tried (which are not working of course):
SELECT * FROM Certificates FULL OUTER JOIN Issuers ON Certificates.issuer_id = Issuers.id; SELECT * FROM Certificates SELECT Issuers.name AS issuer_name LEFT OUTER JOIN Issuers on Certificates.issuer_id = Issuers.id SELECT * FROM Certificates SELECT name from Issuers as cert_name CROSS JOIN Issuers on Certificates.issuer_id = Issuers.id
Can somebody help?