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