How do I create foreign keys and join my tables?

1

Hello!
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?

2

Hello web-coders,

I am not particularly proficient in SQL. Could the issue be that you have the same name for the id identifier in both tables?

Hope this helps.

4

My first question about your code is actually in your Certificates table you are setting the column id as the foreign key, shouldn’t that be the issuers_id column?

As for your queries, full outer joins are not possible in MySQL, though you can emulate one:

SELECT * FROM Certificates
LEFT JOIN Issuers ON Certificates.issuer_id = Issuers.id
UNION ALL
SELECT * FROM Certificates 
RIGHT JOIN Issuers ON Certificates.issuer_id = Issuers.id
WHERE Certificates.issuer_id IS NULL;

SQL Fiddle

The others seem to just be a simple error of syntax:

SELECT Issuers.name AS issuer_name FROM Certificates
LEFT OUTER JOIN Issuers on Certificates.issuer_id = Issuers.id;

SQL Fiddle

SELECT Issuers.name AS cert_name FROM Issuers
CROSS JOIN Certificates ON Certificates.issuer_id = Issuers.id;

SQL Fiddle

Not sure what you’re looking to get as a response from the queries, hopefully this at least points you in the direction you need if nothing else :slight_smile:

5

Yup I did a mistake.
I’m sorry I’m of those who always hurry up to do everything.
So I studied SQL, run some examples.
This is what I wanted to do:

SQL Fiddle: Certificates

I even joined 4 different tables
SQL Inner Join with 4 Tables

Can I do the same things with NoSQL databases?

PS)
This tool was very helpful:

The developer is very nice. He/She provides the source code in GitHub
So I self-hosted it here:
http://generatedata.tk
Which doesn’t have the limitations.