SQLite Join 2 tables with table1 having two columns that reference table2

Question

I have two tables:

jobs

CREATE TABLE IF NOT EXISTS jobs (
    job_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    job_name TEXT,
    prime_desc INTEGER NOT NULL REFERENCES descriptions(desc_id),
    scnd_desc INTEGER NOT NULL REFERENCES descriptions(desc_id),
);
INSERT INTO jobs (job_name, prime_desc, scnd_desc) VALUES ('Soldier', 1, 5);
INSERT INTO jobs (job_name, prime_desc, scnd_desc) VALUES ('Pastor', 2, 3);
INSERT INTO jobs (job_name, prime_desc, scnd_desc) VALUES ('Firefighter', 5, 4);

descriptions

CREATE TABLE IF NOT EXISTS descriptions (
    desc_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    desc_name TEXT,
);
INSERT INTO descriptions (desc_name) VALUES ('Strong');
INSERT INTO descriptions (desc_name) VALUES ('Pious');
INSERT INTO descriptions (desc_name) VALUES ('Honest');
INSERT INTO descriptions (desc_name) VALUES ('Agile');
INSERT INTO descriptions (desc_name) VALUES ('Brave');

In this example, I would like to be able to add two descriptions to a job. The code works and I can query it:

SELECT * FROM jobs;

This returns the expected result:

1|Soldier|1|5
2|Pastor|2|3
3|Firefighter|5|4

What I have been trying to do is get a query that lists the job name and the primary and secondary description names:

Firefighter|Brave|Agile

I have not been able to get that result.

I have tried:

SELECT
    jobs.job_name,
    desc.desc_name
FROM
    jobs
JOIN
    descriptions ON jobs.prime_desc = descriptions.desc_id
JOIN
    descriptions ON jobs.scnd_desc = descriptions.desc_id;

This returns an error:

Parse error: ambiguous column name: descriptions.desc_name
  SELECT jobs.job_name, descriptions.desc_name FROM jobs JOIN descriptions ON...
          error here ---^

I searched the Internet on that error and couldn't find anything that is an example of what I'm trying to accomplish.

I tried many different combinations of:

RIGHT JOIN, LEFT JOIN, UNION, UNION ALL, ALIAS

in multiple configurations. The closest I came is with UNION, but it did not output the results as desired; here's the UNION I tried:

SELECT
    jobs.job_name, descriptions.desc_name
FROM
    jobs
JOIN
    descriptions ON jobs.prime_desc = descriptions.desc_id
UNION
SELECT
    jobs.job_name, descriptions.desc_name
FROM
    jobs
JOIN
    descriptions ON jobs.scnd_desc = descriptions.desc_id;

This returns:

Soldier|Strong
Pastor|Pious
Firefighter|Brave
Soldier|Brave
Pastor|Honest
Firefighter|Agile

It's returning the results as asked, so I know it's something I'm doing wrong. I just don't know what it is that I'm doing that won't print the results in one line per job, as illustrated by the Firefighter example above.

I've tried creating a column alias, a table alias, et al. I'm at a loss as to how to approach this. Any help is appreciated.

score 0 · Answer 1 · answered Jul 11 '24 at 21:54

0

You reference the table description twice, so you need to assign each reference a unique alias.

answered Jul 11 '24 at 21:54

mustaccio

28,207
24
60
76

score 0 · Accepted Answer · answered Jul 11 '24 at 22:48

You can not join the same table twice or more, so use Aliases, so that the database understands, what you want

SELECT
    jobs.job_name,
    desc1.desc_name
  ,desc2.desc_name
FROM
    jobs
JOIN
    descriptions desc1 ON jobs.prime_desc = desc1.desc_id
JOIN
    descriptions desc2 ON jobs.scnd_desc = desc3.desc_id;

SQLite Join 2 tables with table1 having two columns that reference table2

2 Answers2