How do I create foreign keys and join my tables?

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?

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.

What exactly do you mean by “combine my tables”? What specific fields do you want to include in the query result?

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:

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:
https://generatedata.com

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.