The correct way to select rows from either side of a bridge table

join;sqlite

I want to do an SQL query for three columns. The database has five tables, two of which are bridge tables for the other three. When I attempt to select from the tables, I get a very large number of rows that do not necessarily have related columns, which is a problem for me. My question is: how do I select related columns from three tables whose rows are related through a bridge table? For example: I have tables of students, classes, and teachers for those classes linked through bridge tables. I want to select students, their classes, and who teaches those classes from those tables.

In reality I have three tables: items, tags, and categorys and two bridge tables to join them: itemTagBridge and tagCategoryBridge. The idea is that the items in the items table can be given one or more tags which are stored in the tags table and the tags can be assigned one or more categories which are stored in the categorys table. The itemTagBridge table has columns for the item and tag it is joining and the tagCategoryBridge table has columns for the tag and category that it is joining. I would like to select the items, tags, and categorys assigned to those tags from the database. I am using SQLite3. The program I am temporarily using to solve this problem is called SQLite Studio. The current state of the database structure looks like the following:

items:
    id INTEGER PRIMARY KEY
    item TEXT

tags:
    id INTEGER PRIMARY KEY
    tag TEXT

categorys:
    id INTEGER PRIMARY KEY
    category TEXT

itemTagBridge:
    id INTEGER PRIMARY KEY
    itemId INTEGER FOREIGN KEY REFERENCES items (id)
    tagId INTEGER FOREIGN KEY REFERENCES tags (id)

tagCategoryBridge:
    id INTEGER PRIMARY KEY
    tagId INTEGER FOREIGN KEY REFERENCES tags (id)
    categoryId INTEGER FOREIGN KEY REFERENCES categorys (id)

The data I am testing is the following:

items:
    1    "Hello World!"
    2    "Goodbye World!"

tags:
    1    "positivity"
    2    "hello"
    3    "negativity"
    4    "goodbye"
    5    "helloWorld"
    6    "goodByeWorld"

categorys:
    1    "keyword"
    2    "title"

itemTagBridge:
    1    1    1
    2    1    2
    3    1    5
    4    2    3
    5    2    4
    6    2    6

tagCategoryBridge:
    1    1    1
    2    2    1
    3    3    1
    4    4    1
    5    5    2
    6    6    2

In theory, I should be able to select the item, tag, and category columns from the tables items, tags, and categorys tables by joining the bridges to their respective tables like so:

SELECT i.item, t.tag, c.category
FROM items as i, tags as t, categorys as c
INNER JOIN itemTagBridge AS it ON i.id = it.itemId -- Join 1st side of "it" bridge
INNER JOIN itemTagBridge ON t.id= it.tagId -- Join 2nd side of "it" bridge

INNER JOIN tagCategoryBridge AS tc ON  t.id = tc.tagId -- Join 1st side of "tc" bridge
INNER JOIN tagCategoryBridge ON c.id = tc.categoryId; -- Join 2nd side of "tc" bridge

My problem is that the above SQL selects 216 rows when it should select much less. When SELECT DISTINCT is used, 6 rows are selected. When GROUP BY is used, a minimum of 6 rows can be selected also. Another problem with the selection is that the rows selected do not match up correctly (hello is not a title, hello is a keyword, helloWorld is a title).

The 6 rows:

item:           tag:            category:
Goodbye World   goodByeWorld    keyword
Goodbye World   goodbye         keyword
Goodbye World   negativity      keyword
Hello World     helloWorld      keyword
Hello World     hello           title
Hello World     positivity      title

I have been thinking that I have a gross misunderstanding of joins, am unaware of an SQL operation that would be appropriate to use in this case, need to use a different schema, need to use a different database management system, or am completely missing something else with this problem.
My question is how would I select the item, tag, and category from this database, or if that is impossible, nonoptimal, or impractical what would be the better solution?

Thank you for all your help, I'm new to SQL and I've been lost here for about a week with no end in sight.

EDIT:

DDL

-- Drop tables
DROP TABLE IF EXISTS itemTagBridge;
DROP TABLE IF EXISTS tagCategoryBridge;

DROP TABLE IF EXISTS items;
DROP TABLE IF EXISTS tags;
DROP TABLE IF EXISTS categorys;


-- Create tables
CREATE TABLE items(
    id INTEGER PRIMARY KEY,
    item TEXT);

CREATE TABLE tags(
    id INTEGER PRIMARY KEY,
    tag TEXT);

CREATE TABLE categorys(
    id INTEGER PRIMARY KEY,
    category TEXT);


CREATE TABLE itemTagBridge(
    id INTEGER PRIMARY KEY,
    itemId INTEGER REFERENCES items (id),
    tagId INTEGER REFERENCES tags (id));

CREATE TABLE tagCategoryBridge(
    id INTEGER PRIMARY KEY,
    tagId INTEGER REFERENCES tags (id),
    categoryId INTEGER REFERENCES categorys (id));

DML

-- Insert test data into tables
INSERT INTO items (item)
VALUES ("Hello World!"), ("Goodbye World!");

INSERT INTO tags (tag)
VALUES ("positivity"), ("hello"), ("negativity"), ("goodbye"), ("helloWorld"), ("goodByeWorld");

INSERT INTO categorys (category)
VALUES ("keyword"), ("title");


INSERT INTO itemTagBridge (itemId, tagId)
Values (1, 1), (1, 2), (1, 5), (2, 3), (2, 4), (2, 6);

INSERT INTO tagCategoryBridge (tagId, categoryId)
VALUES (1, 1), (2, 1), (3, 1), (4, 1), (5, 2), (6, 2);

Best Answer

To solve your problem, I did the following:

Constructed your tables and data as shown in the fiddle here. Also shown at the bottom of this answer.

For your joining tables (or Associative Entities), I made the PRIMARY KEY the combination of the two joining fields - a separate INTEGER PRIMARY KEY is superfluous for reasons outlined here and here. (It's a mistake I used to make as well! :-) ).

On SQLite 3.8, I then ran this query (fiddle):

SELECT i.item, t.tag, c.category
FROM items i
JOIN itemTagBridge itb ON i.id = itb.itemID
JOIN tags t ON itb.tagID = t.id
JOIN tagCategoryBridge tcb ON itb.tagId = tcb.tagId
JOIN categorys c ON tcb.categoryId = c.id;

Result:

item            tag           category
Hello World!    positivity    keyword
Hello World!    hello         keyword
Hello World!    helloWorld    title
Goodbye World   negativity    keyword
Goodbye World   goodbye       keyword
Goodbye World   goodByeWorld  title
6 rows

Which appears to be the result you want.

I then ran your own query (fiddle)

SELECT i.item, t.tag, c.category
FROM items as i, tags as t, categorys as c
INNER JOIN itemTagBridge AS it ON i.id = it.itemId -- Join 1st side of "it" bridge
INNER JOIN itemTagBridge ON t.id= it.tagId -- Join 2nd side of "it" bridge

INNER JOIN tagCategoryBridge AS tc ON  t.id = tc.tagId -- Join 1st side of "tc" bridge
INNER JOIN tagCategoryBridge ON c.id = tc.categoryId; -- Join 2nd side of "tc" bridge

and it indeed does produce 216 results , 6 sets of 36 of the same result.

36 = 6 * 6 which is the number of fields you have in you joining tables - so you appear to be doing some sort of CROSS JOIN between them, and 36 * 6 = 216, so you appear to be joining tags 3 times (6 records in tags) with the other tables and your joining tables both have tags.

What you want to do is join item to tag to category by means of the joining tables. You appear to have joined your bridging tables twice instead of only once which has led to the multipicative effect that we see in your 216 record resultset.

With your table constructs, all you want to do is simply join across by ids which as the linking fields between the tables. In this final fiddle, I have dissected both my query and yours line by line. I have found this a very effective method of learning in the past. HTH and welcome to the forum!

================= DDL and DML for tables ===================

In future, as requested in the comment, please supply tables and data as DDL and DML - help us to help you! There are some articles on asking questions on this forum on my profile - you might want to take a look?

CREATE TABLE items
(
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    item TEXT
);

CREATE TABLE tags
(
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    tag TEXT
);

CREATE TABLE categorys
(
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    category TEXT
);

CREATE TABLE itemTagBridge
(
    itemId INTEGER,
    tagId INTEGER, 
    PRIMARY KEY (itemId, tagId),
    FOREIGN KEY (itemID) REFERENCES items (id),
    FOREIGN KEY (tagId)  REFERENCES tags (id)
);

CREATE TABLE tagCategoryBridge
(
    tagId INTEGER,
    categoryId INTEGER,  
    PRIMARY KEY (tagId, categoryId),
    FOREIGN KEY (tagId) REFERENCES tags (id),
    FOREIGN KEY (categoryId) REFERENCES categorys (id)
);

INSERT INTO items (item)
VALUES ('Hello World!'), ('Goodbye World');

INSERT INTO categorys (category) VALUES ('keyword'), ('title');

INSERT INTO tags (tag)
VALUES ('positivity'), ('hello'), ('negativity'), ('goodbye'), ('helloWorld'), ('goodByeWorld');

INSERT INTO itemTagBridge
VALUES(1, 1), (1, 2), (1, 5), (2, 3), (2, 4), (2, 6);

INSERT INTO tagCategoryBridge
VALUES (1, 1), (2, 1), (3, 1), (4, 1), (5, 2), (6, 2);
Related Question