I have problems with a query I know to be rather simple, but I'm away from SQL for quite some time now, so it's giving me a real headache.
I have a table PARTS which contains parts with START and ENDING, ENDING can be NULL. Every part belongs to a certain structure so it has a STRUCTURE_ID as well. Structures and their values are stored in table B, one of these values is PLANT_ID.
The request is that a part can only belong to one structure at a time, timely intersections are not allowed. Please not that the part itself can occur several times in A because it still can have the same time contraints belonging to different plants.
So, now I'd like to finde "duplicates", parts belonging to the same or a different structure at the same time in the same plant. I have no problem with the time constraint, the check for the plant is where I fail. I know I have to do something like:
SELECT DISTINCT C.PLANT_ID, C.NAME AS NAME_TF, A.STRUCTURE_ID, A.START, A.ENDING, A.PART_NO FROM PARTS A, PARTS B, STRUCTURES C WHERE ((A.START = B.START AND A.ENDING = B.ENDING) OR (A.ENDING IS NULL AND B.ENDING IS NULL) OR (A.ENDING IS NULL AND B.ENDING IS NOT NULL AND A.START<= B.ENDING) OR (A.START <= B.START AND A.ENDING>= B.START) OR (A.START >= B.START AND A.START<= B.ENDING) )
AND A.STRUCTURE_ID = C.STRUCTURE_ID <= problem starts here... AND B.STRUCTURE_ID = C.STRUCTURE_ID AND... C.STRUCTURE_ID = C.STRUCTURE_ID is nonsense, of course.
I would like to do something like a.getStructreId == b.getStructreId I'm to OO, I'm afraid. By the way, this is for a one-time query, so performance is not that important.
I remember those days where I could do such a query with ease...*sigh*