Home > Archive > PHP SQL > October 2005 > Get ID's from two different tables
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Get ID's from two different tables
|
|
| Nail Ünlü 2005-10-29, 7:00 pm |
| 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
| |
| ZeldorBlat 2005-10-30, 3:56 am |
| 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
| |
| Nail Ünlü 2005-10-30, 7:56 am |
| 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
| |
| ZeldorBlat 2005-10-30, 6:58 pm |
| >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)
|
|
|
|
|