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:
categorys and two bridge tables to join them:
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.
-- 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));
-- 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);