Android 在 SQLite 数据库中加入 3 个表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11321354/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Join 3 tables in SQLite database
提问by Eng.Fouad
I have 3 tables in SQLite database:
我在 SQLite 数据库中有 3 个表：
Songs:
歌曲：
_id | name | length | artist_id (foreign key) | album_id (foreign key)
Artists:
艺术家：
_id | name
Albums:
专辑：
_id | name
I need a query (use it in an Android app) of a table that consists of the following columns:
我需要一个由以下列组成的表的查询（在 Android 应用程序中使用它）：
_id | name | length | artist_id | artist_name | album_id | album_name
However, I write the following query statement:
但是，我编写了以下查询语句：
SELECT Songs._id, Songs.name, Songs.length, Songs.artist_id, Artists.name, Songs.album_id, Albums.name FROM Songs, Artists, Albums WHERE Songs.artist_id = Artists._id AND Songs.album_id = Albums._id
but it gives me an empty table. I tried ORinstead of ANDand it gives incorrect results (every song duplicates in each album, though the artist is correct). How can I fix my query statement to join the 3 tables in a single table?
但它给了我一张空桌子。我尝试了OR而不是AND它给出了错误的结果（每张专辑中的每首歌都重复，尽管艺术家是正确的）。如何修复我的查询语句以将 3 个表连接到一个表中？
回答by Michael Berkowski
Using an explicit JOINinstead of an implicit one, the following should get what you want, although it is curious that your implicit join syntax did not return correct results in the first place. I have used a LEFT JOIN, to account for songs which do not have an associated artist or album, but that may not be necessary for your data set and an INNER JOINcould be used instead.
使用显式JOIN而不是隐式，以下应该得到您想要的，尽管奇怪的是您的隐式连接语法首先没有返回正确的结果。我使用了LEFT JOIN, 来说明没有关联艺术家或专辑的歌曲，但这对于您的数据集可能不是必需的，INNER JOIN可以使用 。
I have also added column aliases to eliminate ambiguity when fetching rows, since you have similar column names in most of your tables (id, name).
我还添加了列别名以消除获取行时的歧义，因为在大多数表 ( id, name) 中都有相似的列名。
SELECT
Songs._id AS song_id,
Songs.name AS song_name,
Songs.length,
Songs.artist_id AS artist_id,
Artists.name AS artist_name,
Songs.album_id AS album_id,
Albums.name AS album_name
FROM
Songs
LEFT JOIN Artists ON Songs.artist_id = Artists._id
LEFT JOIN Albums ON Songs.album_id = Albums._id
回答by Gabriel Lucas
Try this select, may by the Artistsis more important than others, so the Songscome trough Artistsand Albumsfrom Songs.
试试这个选择，可以通过Artists比其他人更重要，所以Songs来槽Artists和Albums从Songs。
SELECT
Songs._id AS song_id,
Songs.name AS song_name,
Songs.length,
Songs.artist_id AS artist_id,
Artists.name AS artist_name,
Songs.album_id AS album_id,
Albums.name AS album_name
FROM
Artists
LEFT JOIN Songs ON Songs.artist_id = Artists._id
LEFT JOIN Albums ON Songs.album_id = Albums._id
Also if there is no entry in Songsbelonging to a particular artist or no entry in Albumsbelonging to a particular song, you will still get the artist entry thanks to the LEFT JOIN. If you would like to return only artists with songs and albums, use JOINinstead.
此外，如果没有Songs属于特定艺术家的条目或没有Albums属于特定歌曲的条目，由于LEFT JOIN. 如果您只想返回带有歌曲和专辑的艺术家，请JOIN改用。
回答by user1492669
try sql inner join
尝试 sql 内连接
inner join Artists on Songs.artist_id = Artists._id
