Get ID"s from two different tables

Hiho,

I have the following problem. Two tables which have the same primary key
"statusid". I one table i have 10 entries and in the other 7. The 7 from
the second table are also in the first. What i want to do is to get
the other 3.

As the 7 are flagged with a special value, i want to get the other 3,
which dont have this flag.

I know that i can do a UNION to get distinct values from two tables but
i cant do that as the fields are not the same beside the statusid.

To simplfy it:

Table1
-----------------------
statusid | flag |
2123123 | A |
2342342 | B |
3450983 | C |


Table2
-------------------------------
statusid | other fields... |
2123123 | ... |
2342342 | ... |
3450983 | ... |
0980234 | ... |
2342344 | ... |



i hope someone can help me out on this

cu,
Nail

Re: Get ID"s from two different tables

It sounds like you want to outer join the tables to that you get all
the rows, even if they don't have a flag. So if, according to your
example, Table2 is the one with 10 rows and Table1 is the one with 7
rows, you could do this:

select t2.*, t1.flag
from table2 t2
left outer join table1 t1
on t2.statusid = t1.statusid

Re: Get ID"s from two different tables

ZeldorBlat schrieb:
> It sounds like you want to outer join the tables to that you get all
> the rows, even if they don't have a flag. So if, according to your
> example, Table2 is the one with 10 rows and Table1 is the one with 7
> rows, you could do this:
>
> select t2.*, t1.flag
> from table2 t2
> left outer join table1 t1
> on t2.statusid = t1.statusid
>

i forgot to mention that table2 has much more records but only 10 that
would much table1 id...

actually the primary key on both tables is a composed key from 2 fields
id ans shipref....and in table1 i have only 7 records and on
table2...over 6Mio...

It should only get the records from t2 which have
t2.statusid=t1.statusid AND t2.shipref=t1.shipref ...but this doesnt
seem to work with the left outer join...


cu,
Nail

Re: Get ID"s from two different tables

>It should only get the records from t2 which have
>t2.statusid=t1.statusid AND t2.shipref=t1.shipref ...but this doesnt
>seem to work with the left outer join...

select t2.*, t1.flag
from table2 t2
left outer join table1 t1
on (t2.statusid = t1.statusid
and t2.shipref = t1.shipref)