mardi 1 mars 2016

SQLite Query 3+ Tables With Conditions - My Solution Works? No Errors

at the moment I am learning sqlite and I have been working with sqlite manager.

I have to SELECT all Projects which have been assigned by a City at least 2 times.

I have the following Tables which I can work with:

CREATE TABLE "Project" 
("Project_ID" INTEGER PRIMARY KEY  NOT NULL , "Name" TEXT, "Year" INTEGER)

The Collections (Projects are stored in collections):

CREATE TABLE "Collection" ("Collection_ID" INTEGER PRIMARY KEY  NOT NULL , "Name" TEXT)

The Relation ProjectsCollection:

CREATE TABLE "ProjectsCollection" ("Collection_ID" INTEGER, "Project_ID" 
INTEGER, FOREIGN KEY(Collection_ID) REFERENCES Collection(Collection_ID),
FOREIGN KEY(Project_ID) REFERENCES Project(Project_ID), 
PRIMARY KEY(Collection_ID, Project_ID))

The Relation RealisedIn shows in which Cities projects have been realised:

CREATE TABLE "RealisedIn" ("Collection_ID" INTEGER, "City_ID" INTEGER, 
FOREIGN KEY(Collection_ID) 
REFERENCES Collection(Collection_ID), FOREIGN KEY(City_ID) REFERENCES City(City_ID), 
PRIMARY KEY(Collection_ID, City_ID))

And the last Table are the Cities:

CREATE TABLE "City" ("City_ID" INTEGER PRIMARY KEY  NOT NULL , "Name" TEXT)

So as far as I understand this, I have to make many JOINS. The main table is the RealisedIn Table because we can see here how ofter a certain city asked for a project to be realised but the Projects are connected to the Collection, so we see for example Collection 1 and Collection 2 have been asked both by City New York. Now we move to the ProjectsCollection to check whether Collection 1 and 2 store the same Project, if its true, we have a match, means New York asked for the same Project twice. All the other Tables are just for help. I have a solution:

SELECT *
FROM Project x1
JOIN ProjectsCollection x2 ON
x1.Project_ID = x2.Project_ID
JOIN RealisedIn x3 ON
x2.Collection_ID = x3.Collection_D
GROUP BY x3.City_ID
HAVING COUNT(*) >= 2

I dont get any Errors and even the right city but multiple times, thats weird. It really seems like a complex question. If someone takes the time, that would be awesome.

Thanks

Aucun commentaire:

Enregistrer un commentaire