mardi 1 mars 2016

SQLite Query Display Name with WHERE condition - Multiple Tables

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