On 2008-01-14 Patric wrote: > Well I've a normalized database.. > For instance: > > create table Y ( pk, data... ); > create table Z ( pk , data... ); > > create table X ( char, references Y, references Z); > > SELECT * from X; > > Now I want to make a listing of the result set from X. > If there are references to Z or Y (not null refs), I want to display > that data too. > > Normally I would SELECT, to get that data, not in my case. > Nearly all queries will be SELECTs, no UPDATEs or INSERTs, so need to > optimize that case. > > The dirty little denormalization would look like this: > > create table X ( char, ref. to Y, ref. to Z, StoreY Y , StoreZ Z); > > On insert or update of Z or Y, I would update these two (StoreY, > StoreZ) columns by RULE or TRIGGER.. > I know this is not nice etc.. Codd would sue for this, but in my case > performance over beauty is ok. > I'm looking for something like UPDATE X set StoreY=(SELECT * FROM Y > WHERE pk=4) WHERE foreignID2Y = 4; > > Is there a away to accomplish this straightforward in a single > statement without doing loops and stuff in a serverside procedure? Looks to me like you want to (LEFT|RIGHT) OUTER JOIN the tables. Regards Ansgar Wiechers -- "The Mac OS X kernel should never panic because, when it does, it seriously inconveniences the user." --http://developer.apple.com/technotes/tn2004/tn2118.html ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
