Advanced SQL/Oracle Question

um7y1h83ge06nx

Hi Lads,

Quick question here. I'm not lazy enough to be looking for a neatly packaged solution or anything , just a few pointers in the right direction.

My problem is I have two tables, where the second one has a description to be associated with a name on the first table.

The 'name' exists in a full form on the first table e.g. John Smith but the 'name' on the second may be the full name or an initial string ending in a % wildcard.

There is also the issue of the entries on the second table being public or private.

I want to associate (join) the description on the second table with the entries on the first table with the following type of hierarchy:

# Public wildcard e.g. John%
which can be overridden by
# Public fully qualified e.g. John Smith
which can be overridden by
# Private wildcard e.g. John%
which can be overridden by
# Private fully qualified e.g. John Smith

Is there any way to do this with some advanced well constructed SQL statement, or am I looking at multiple SQL statements and then business logic in my server side code (Java)?

Any help appreciated.

Tom Dunne

Looks trivial enough, but I don't understand what you mean by "overridden".

um7y1h83ge06nx

Probably a bad choice of words.

What I mean is if the second table is something like this:

Private John Smith "My full private description for John Smith"
Public John% "Public description for all Johns"
Public John Smith "Public description for John Smith"
Private John S% "My Private description for all John S's"

I want the SQL statement to join the description "My full private description for John Smith" to John Smith's entry on the first table, as that would be the most accurate description, with the Public John% being the least accurate.

I don't know however what the entries are on the second table, just that I want the more 'accurate' descriptions used over the less 'sccurate' one.

Can SQL do this sort of hierarchy rule?

Thanks.

showry

What's the structure of the tables?

Can you provide some sample data from both tables?

amen

are all the entries in the one column on the second table?
sample data would be helpful?

Tom Dunne

Freya Shapely Killer wrote: »

Probably a bad choice of words.

What I mean is if the second table is something like this:

Private John Smith "My full private description for John Smith"
Public John% "Public description for all Johns"
Public John Smith "Public description for John Smith"
Private John S% "My Private description for all John S's"

I want the SQL statement to join the description "My full private description for John Smith" to John Smith's entry on the first table, as that would be the most accurate description, with the Public John% being the least accurate.

I don't know however what the entries are on the second table, just that I want the more 'accurate' descriptions used over the less 'sccurate' one.

Can SQL do this sort of hierarchy rule?

Still not fully getting what you are after, but sample data and table structure would help immensely.

I am beginning to think you need four CASE statements:

CASE Private John Smith -> 'My Full private description....'
CASE Public John% -> 'Public description for all johns....'

and so on.