加入3表中的SQLite数据库 [英] Join 3 tables in SQLite database

问题描述

我有3个表在SQLite数据库：

I have 3 tables in SQLite database:

歌曲：

_id | name | length | artist_id (foreign key) | album_id (foreign key)

艺术家：

_id | name

专辑：

_id | name

我需要一个表，其中包含以下各列的查询（使用它在一个Android应用程序）：

I need a query (use it in an Android app) of a table that consists of the following columns:

_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

但它给了我一个空表。我试过或而不是和，它给了不正确的结果（每首歌曲重复的每张专辑，虽然艺术家是正确的）。我怎样才能解决我的查询语句加入3个表在单个表？

but it gives me an empty table. I tried OR instead of AND and 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?

推荐答案

使用明确的加入，而不是隐式的，下面应该得到你想要的东西，虽然它，奇怪的是，你的隐式连接语法并没有在第一时间返回正确的结果。我使用了一个 LEFT JOIN ，考虑到不具有关联的艺术家或专辑的歌曲，但可能没有必要为您的数据集和一个 INNER JOIN 可以用来代替。

Using an explicit JOIN instead 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 JOIN could be used instead.

我还添加了列别名获取行时，以消除歧义，因为你也有类似的列名在大部分的表（的ID，名称）。

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).

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

这篇关于加入3表中的SQLite数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！