File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes SQL help: looking for duplicates referencing two tables Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "SQL help: looking for duplicates referencing two tables" Watch "SQL help: looking for duplicates referencing two tables" New topic
Author

SQL help: looking for duplicates referencing two tables

Mike Himstead
Ranch Hand

Joined: Apr 12, 2006
Posts: 178
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
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30768
    
156

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


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Mike Himstead
Ranch Hand

Joined: Apr 12, 2006
Posts: 178
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
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30768
    
156

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.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: SQL help: looking for duplicates referencing two tables