Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.2k views
in Technique[技术] by (71.8m points)

sqlite - Complex query on sql lite xcode

I'm developing an iOS app and I have a sqlite database with 2 tables related by 1-to-many relationship.

Now I would like to do a query that retrieve all element by first table and in the same time do a count by second table so I can pass the result into my view.

CREATE TABLE track(
    trackid     INTEGER, 
    trackname   TEXT, 
    trackartist INTEGER,
    FOREIGN KEY(trackartist) REFERENCES artist(artistid)
);

CREATE TABLE artist(
    artistid    INTEGER PRIMARY KEY, 
    artistname  TEXT
);

I would like to create a query that returns all artist name and the count of track for each artist name so I can pass this value to my list.

Is it possible? Any help? Thanks to Joe, your code works well for my, but it's possibile to add new field for store the result of count?

Sorry and if i would take the also all trackname for each artist in the same query?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)
SELECT a.artistname, count(*)
FROM track t
INNER JOIN artist a
   on  t.trackartist = a.artistid
GROUP BY a.artistid

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...