aspose file tools*
The moose likes JDBC and the fly likes SQL Question Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "SQL Question" Watch "SQL Question" New topic
Author

SQL Question

M Jay
Ranch Hand

Joined: Sep 21, 2004
Posts: 66
Guys,

Please help with the following scenario:

I have a Java Program that will make an executeQuery for an SQL statement, but this query is actually quite hard to formulate:
I have 6 database tables: Person, Person_History, Address, Address_history, Preferences and Preferences_history. The primary key for Person and Person_hist is record_number and all other tables are linked by record_number as foreign key. My java program will get a set of input parameters between 3-10, which might be a combination of Person/HIST, Address/HIST and Preferences/HIST attributes. The input might be current or historic but the Java program has no knowledge of that. I need to use the attributes in SQL to get a matching record_number from the database but this is proving to be a hard task.

Consider the following scenario:

attributes provided: name(person entity)
address(address entity)
pref(preference entity)

Now to get a person matching the above attributes, we need to search Person + Person_hist(as the name could be current or historic), address+address_history(as the provided address_line1 could be current or historic), preferences + preferences_hist(as the preference could be current or historic). Obviously these need to be joined but its not very easy... Can somebody tell me of a good way to do it?

P.S. quering each table seperately is not an option as searching for a name "David" for example will return too many matches which will effect the perofrmance. PERFORMANCE IS AN ISSUE as the application is HUGE SCALE!


SCJP J2SE 1.4<br />SCBCD J2EE 1.3
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30752
    
156

The following query is untested (I didn't type it into the command line, but I've used it in the past) and uses db2 specific syntax. It can be modified for other databases though. The general idea is to at least keep the query on the database side and reduce the network traffic. This is going to be an expensive query regardless of what you do, so make sure you have good indexes.



[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
Avi Abrami
Ranch Hand

Joined: Oct 11, 2000
Posts: 1134

M,
I understand from your description that either a person exists in the PERSON table or the PERSON_HISTORY table -- but not in both.

Similarly, the same person's address will exist either in the ADDRESS table or the ADDRESS_HISTORY table -- but not in both.

(And of-course the same applies for the person's preferences.)

I also understand that combining a person's name, address and preferences will uniquely identify that person.

Assuming the above is correct, here are some DDL statements to describe what I think your table structures are:
[Please note that I have never used DB2 -- I use Oracle. Hence the syntax may not be what you are used to -- but I hope sufficiently understandable so as to be of help.]

(I am assuming that the "history" tables have similar structures.)

Now the query should be:

Hope it helps.

Good Luck,
Avi.
M Jay
Ranch Hand

Joined: Sep 21, 2004
Posts: 66
Avi,

Thank you for your reply.

All your assumptions were absolutely spot on.

HOWEVER... your query does not account for situations where part of the input is current and part historic, e.g. the address is current while the preference is historic. The query has to account for these situations, while your query will only get a match if the attributes are all current OR all historic.

So if I follow your way, then I will have to write 8 queries (to account for all different combinations) and join them with UNION ALL to make up one query. However that does not sound very practical, and there will be situations where for example no preference is provided in the input, which means I will run through 8 queries while I only need to run through 4 (N.B. each of the 8 queries will be built at run time so for example when preference is not provided then the preference-specific part of the query will not be there).

It's quite a tough one isn't it?
M Jay
Ranch Hand

Joined: Sep 21, 2004
Posts: 66
Jeanne,

Thanks a lot for your reply. Your query looks promising, I will try it on Oracle as this is what I'm using, I will let you know how it goes.

Thanks again!
M Jay
Ranch Hand

Joined: Sep 21, 2004
Posts: 66
Jeanne,

Thank you very much, your query worked!! It's amazing how you understood my vague explanation to the problem and provided a quick spot on answer!!
The query was 100% compatible with Oracle and all I had to do is modify it a bit to fit in my model.

Thanks again
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30752
    
156

Originally posted by M Jay:
The query was 100% compatible with Oracle

Good to know!
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: SQL Question