[RESOLVED] selective rows between two tables

[motil]

Hi all

i have a little problem and i want to see if i can address it from sql instead using CODE

i have two tables, the first one is the base table, its structure is something like this:

id,col1,col2,col3

values:

 1, 'some text',int value(5),int value(112)

the second table contains copies of some records from the first table
structure:

id,col1,col2,col3,row_id

values

10,'some other text',int value(5),int value(112),1

the columns "row_id" in table 2 stores the pk id of table 1

that's mean that table 2 contains a copy of the row with the id of 1 from table 1 but with different text value.

what i want to achieve is that with a single select i'll get all rows from table 1 but if there is a copy of any row in table 2 it will replace the original row from table 1 in the select result.

is there any way to do it with pure sql or should i filter them only after i get the result from the two tables ? (i'm trying to save the two select statements)

Thanks and best regards!

[si_the_geek]

I think this is what you want:

SELECT id, col1, col2, col3
FROM table2

UNION

SELECT id,col1,col2,col3
FROM table1
WHERE id NOT IN (SELECT id FROM table2)

[szlamany]

Select T1.Id
    ,IsNull(T2.Col1,T1.Col1) "Col1"
    ,IsNull(T2.Col1,T1.Col2) "Col2"
    ,IsNull(T2.Col3,T1.Col3) "Col3"
    From Table1 T1
    Left Join Table2 T2 on T2.RowId=T1.Id

This ONLY RETURNS rows for every Id in the PK of Table1.

Table2 replaces the values ONLY IF a row exists in that table.

If Table2 has a RowId of 50, lets say - and 50 no longer exists as a Id in Table1 - then 50 will not be included.

Although I think there is a different JOIN method that would get around that...

[motil]

Thanks guys, I'm just about getting to the part where i need to start wrestling with this, i will get back with the results very soon.

[motil]

Hi, it's been a while but i only got to this now and i decided to go with the union option which work perfect in this case, thank you both for the help.