I am currently learning sqlite and I've been working with sqlite manager so far.
I have different tables and want to select all Project Names where 3 or more people have worked on.
I have my project table which looks like this:
CREATE TABLE "Project"
("Project-ID" INTEGER PRIMARY KEY NOT NULL , "Name" TEXT, "Year" INTEGER)
And I have my relation where it is specified how many people work on a project:
CREATE TABLE "Works_on"
("User" TEXT, "Project-ID" INTEGER, FOREIGN KEY(User) REFERENCES People(User),
FOREIGN KEY(Project-ID) REFERENCES Project(Project-ID), PRIMARY KEY(User, Project-ID))
So in the simple view (sadly I can not upload Images) you have something like this in the relation "Works_on":
User | Project-ID
-------+-----------
Greg | 1
Daniel | 1
Daniel | 2
Daniel | 3
Jeny | 3
Mark | 3
Mark | 1
Now I need to select the names of the projects where 3 or more people are working on, this means I need the name of project 3 and 1.
I tried so far to use count() but I can not figure out how to get the names:
SELECT Project-ID, count(Project-ID)
FROM Works_on
WHERE Project-ID >= 3
Aucun commentaire:
Enregistrer un commentaire