Joining tables

blackbox

Hello all,

I have an issue with updating the correct record in a table.

I have a table which holds a number of feilds one of which is also in all the other tables.

I am trying to read for table "Room_Hist" where "SeqID0101" IS NULL

When found update the remaining feilds with data from the other tables but only where the "UniqueID" is the same.

This is the Query I am working on:

SELECT DISTINCT
Room_Hist.UniqueID, Room_Hist.HotelID, Room_Hist.Room, Room_Hist.AuditBy, Room_Hist.LastAudit, Room_Hist.SeqID0101,
Table01.SeqID0101,Table01.SeqID0102,Table01.SeqID0103,Table01.SeqID0104,Table01.SeqID0105,Table01.SeqID0106,
Table01.SeqID0107,Table01.SeqID0108,Table01.SeqID0109,Table01.SeqID0110,Table01.Comments01,Table01.SeqID01Complete,
Table02.SeqID0201,Table02.SeqID0202,Table02.SeqID0203,Table02.Comments02,Table02.SeqID02Complete,
Table03.SeqID0301,Table03.SeqID0302,Table03.SeqID0303,Table03.SeqID0304,Table03.Comments03,Table03.SeqID03Complete,
Table04.SeqID0401,Table04.SeqID0402,Table04.SeqID0403,Table04.SeqID0404,Table04.Comments04,Table04.SeqID04Complete
FROM Room_Hist
Left Join Table01 ON Room_Hist.UniqueID = Table01.UniqueID
Left Join Table02 ON Room_Hist.UniqueID = Table02.UniqueID
Left Join Table03 ON Room_Hist.UniqueID = Table03.UniqueID
Left Join Table04 ON Room_Hist.UniqueID = Table04.UniqueID
Left Join Table05 ON Room_Hist.UniqueID = Table05.UniqueID
WHERE Room_Hist.SeqID0101 IS NULL

The issue at the moment is it updated all the records in the "Room_Hist" table even if the "UniqueID" are not the same.

I know its some thing to do with the way I have constructed the Query. I have changed the way the tables are joined
using all the different methods but I get the same result, all records updated and noyt just the one where the "UniqueID"'s match.

Can anyone see where I am going wrong.

Many thanks in advance.

Regards,

Blackbox

NogDog

Since all the joins are left joins, you'll match every record in Room_Hist where Room_Hist.SeqID0101 is null, regardless if it actually finds any joins or not.

blackbox

Hello NogDog,

Many thanks for your reply.

I have tried all the join types I know but the result is more than one row of data. With an Outer Right Join I the result is the record with the correct "UniqueID" plus two other records without a "UniqueID". Is the query complete rubbish and should I be ding this in another way.

Regards,

Blackbox

NogDog

Is it too late to go back to first causes and decide if it's really desirable to de-normalize the data structure by duplicating data in two places withing the database?

If it is, my next suggestion would be to put triggers on Table01 - Table05 to update RoomHist on update/inserts into those tables (and possibly on deletes, too?)

If that's not possible, then I'm not sure if a join across multiple tables will work (I don't claim to be God's gift to the SQL world -- yet), or if it may need to be broken into 5 separate queries using inner joins -- or maybe 5 sub-queries?

johanafm

SELECT Room_Hist.UniqueID
FROM Room_Hist
Left Join Table01 ON Room_Hist.UniqueID = Table01.UniqueID
Left Join Table02 ON Room_Hist.UniqueID = Table02.UniqueID
Left Join Table03 ON Room_Hist.UniqueID = Table03.UniqueID
Left Join Table04 ON Room_Hist.UniqueID = Table04.UniqueID
Left Join Table05 ON Room_Hist.UniqueID = Table05.UniqueID
WHERE Room_Hist.SeqID0101 IS NULL

1. This will give you a maximum of one single row per unique_id. Well, assuming unique_id is actually unique. If it's not, I sugges you rename it to something else. Moreover, since you get max one row per uniqueid, using DISTINCT won't change anything. And the total result would be exactly one row for each row in RooM_Hist which has seqid01010 (do you really use these names?) NULL and UniqueID NOT NULL.

Thus, it is not a cross join issue.

blackbox;11032009 wrote:
I know its some thing to do with the Query. (…) all records updated and noyt just the one where the "UniqueID"'s match.

2. Your query will not modify anything at all since it's a select query.

blackbox;11032009 wrote:
it updated

which means that your "it" is some other it. Find your it and you have located the issue.