RE: Suggestion with SQL query

Hi
Let me explain what i am trying to do, and the
database i have designed for it,
I have some basic preferences and allow user to over
ride them, for example i can say by default 10 rows
are defined on webpage, and user can over ride to say
20 rows,
so i have 2 tables
T1 (store default preference)
type id value
page1 rows 10
page2 rows 15

T2 (Store and over ride user preference)
type id value user
page1 rows 15 ABC

So when user ABC visits website I want to have a query
to get user and default preferences.

So i wrote a query using union as mentioned by
Michael

select type, id, value from T2 where user = 'ABC'
union
select type, id, value from t1 where not exists
(select '1' from t2 where t1.type = t2.type and t1.id
= t2.id and t2.user = 'ABC')

This will return me result as below
type id value
page1 rows 15 (From T2 as user override)
page2 rows 15 (From T1 as default value)

I am not sure if i can use COALESCE to do what i am
doing here.

Any ideas about the database design and query

Ashish





--- Joe Pluta <joepluta@xxxxxxxxxxxxxxxxx> wrote:

From: Michael_Schutte@xxxxxxxxxxxx

You are right Joe...

I went the route of union because I assumed Ash

wanted records from both

tables. From what I can see, if you join the two

tables, you will miss

the records that are unique in the joined table.

So if table 1 has 1 record and table 2 has 3. And

you join table 2 to

table 1, your result will only be 1 record.

Absolutely right, Michael. Yet another reason why
programming is less about
the language and more about succinctly defining the
business problem! If
there are records in T2 that are not overriding
records in T1, then those
records will not be included in a LEFT OUTER JOIN
(that's the underlying
feature of a JOIN: the primary file drives the data
selection).

My assumption is that all id/type combinations in T2
are also in T1, your
assumption is that they may not be. The technique
chosen will probably rely
on the data (although if the answer is not known,
your solution gets the nod
because it handles both cases).

Joe

--
This is the Java Programming on and around the
iSeries / AS400 (JAVA400-L) mailing list
To post a message email: JAVA400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/java400-l
or email: JAVA400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the
archives
at http://archive.midrange.com/java400-l.

A$HI$H