CODEWITHSUNDEEP
sqliteforeign-keys
robev
robev

Reputation: 1949

Generate list of tables which reference a foreign key

Maybe I'm missing something here but I feel this isn't such an uncommon thing to do in SQL.

I have a DB table1 with a lot of values, and several other tables that each have a foreign key to the primary key of table1. I want for every row in table1 to generate a list of all the tables which reference it.

For example, I have a table called Books, which contains rows describing different books. I then have a table called SchoolLibrary which contains rows of all the books their library has and where they are stored. Not all the books in Books appear in school library. I then have a table called PublicLibrary, which similarly contains rows of all the books they have, as well as information about who checked them out.

I would then want to select all the values in Books and add a column with a list of which libraries reference that book, so it would say either {1, []} or {1, [PublicLibrary]} or {1, [SchoolLibary, PublicLibrary]} for book with id 1, etc...

Perhaps more useful would be not only which table references it, but also the id of the row in that table which references it. So the list would be something like {1, [PublicLibrary: 5]}, so the row with id 5 in PublicLibrary references the book with id 1.

How can I do this?

Upvotes: 1

Views: 66

Answers (1)

robev
robev

Reputation: 1949

After discussing with someone I realized there's a simple solution to this question. Just do a LEFT JOIN of all the tables

SELECT Books._id, SchoolLibrary._id, PublicLibrary._id
FROM Books
LEFT JOIN SchoolLibrary ON Books._id = SchoolLibrary.bookId
LEFT JOIN PublicLibrary ON Books._id = PublicLibrary.bookId

This will return a table with all the values in Books, with a column for the other tables. If the other tables reference Books, the id of which entry references Books will be stored. If it doesn't, it'll be NULL

For example (where SchoolLibrary and PublicLibrary have _id, bookId as columns):

Books        SchoolLibrary        PublicLibrary
-----        -------------        -------------
1            1    |    1          1     |    2
2            2    |    3          2     |    3
3

With the above script, it will return the following:

Book._id        SchoolLibrary._id        PublicLibrary._id
--------        -----------------        -----------------
1               1                        
2                                        1
3               2                        2

Upvotes: 1

Related Questions