SQL help: looking for duplicates referencing two tables

[Mike Himstead]

Hi,

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*

[Jeanne Boyarsky]

Mike,
I think I'm missing the point here. Why can't you do a.getStructreId = b.getStructreId?

[Mike Himstead]

a.getStructreId = b.getStructreId is OO/Java-talk, isn't it? I need to link a's STRUCTURE_ID to it's PLANT_ID in c, same for b, and then I have to compare the two PLANT_IDs.

[Jeanne Boyarsky]

Mike,
I see. If your structure ids had the same meaning, they would be valid to compare. It's not so much Java/OO vs SQL speak as it is the table definitions.

You can still do a join on plant id. Which is the a.plant_id = b.plant_id part.