muratyaman
asked on
Left Join problem
I have 2 tables in a Firebird 1.5 database such as:
TABLE1
F1 F2 F3
============
1 AA 00
2 BB 10
3 CC 15
4 DD 20
TABLE2
F1 F4
======
1 aaa
1 bbb
2 aaa
1 ccc
3 ddd
3 aaa
2 eee
So to join using fields F1
I want to get this output using an SQL command:
F1 F2 F3 FIRST of F4
==================
1 AA 00 aaa
2 BB 10 aaa
3 CC 15 ddd
4 DD 20 NULL
What is the SQL command for this output?
Thanks
TABLE1
F1 F2 F3
============
1 AA 00
2 BB 10
3 CC 15
4 DD 20
TABLE2
F1 F4
======
1 aaa
1 bbb
2 aaa
1 ccc
3 ddd
3 aaa
2 eee
So to join using fields F1
I want to get this output using an SQL command:
F1 F2 F3 FIRST of F4
==================
1 AA 00 aaa
2 BB 10 aaa
3 CC 15 ddd
4 DD 20 NULL
What is the SQL command for this output?
Thanks
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
select F1, F2, F3, (Select first 1 F4 from Table2 where table2.F1 = table1.F1) from Table1
(Select first 1 F4 from Table2 where table2.F1 = table1.F1) - but you can't ensure the required ordering
to just pick the first
to just pick the first
ASKER
Thanks
nickupson, the order is not important at the moment; just (any) first value is enough.
yes we can add another column to sort and get record more appropriately.
ezraa, you have the right query.
but actual problem is a bit harder :)
there is another field F5 as well. so when we get the first record we need to get the couple.
TABLE2
F1 F4 F5
===========
1 aaa x
1 bbb y
2 aaa w
1 ccc z
3 ddd x
3 aaa u
2 eee v
so I used a similar query to ezraa's like:
select F1, F2, F3,
(Select first 1 F4 from Table2 where table2.F1 = table1.F1),
(Select first 1 F5 from Table2 where table2.F1 = table1.F1)
from Table1
to get
F1 F2 F3 FIRST of F4 FIRST of F5
==========================
1 AA 00 aaa x
2 BB 10 aaa w
3 CC 15 ddd x
4 DD 20 NULL NULL
so according to this example,
can I make sure that Firebird ALWAYS gives me the right couple (F4, F5)?
(maybe, this is related to Firebird's interpretation of FIRST keyword.)
e.g. Talking for first row, it does NOT give y instead of x, right?
Thanks
nickupson, the order is not important at the moment; just (any) first value is enough.
yes we can add another column to sort and get record more appropriately.
ezraa, you have the right query.
but actual problem is a bit harder :)
there is another field F5 as well. so when we get the first record we need to get the couple.
TABLE2
F1 F4 F5
===========
1 aaa x
1 bbb y
2 aaa w
1 ccc z
3 ddd x
3 aaa u
2 eee v
so I used a similar query to ezraa's like:
select F1, F2, F3,
(Select first 1 F4 from Table2 where table2.F1 = table1.F1),
(Select first 1 F5 from Table2 where table2.F1 = table1.F1)
from Table1
to get
F1 F2 F3 FIRST of F4 FIRST of F5
==========================
1 AA 00 aaa x
2 BB 10 aaa w
3 CC 15 ddd x
4 DD 20 NULL NULL
so according to this example,
can I make sure that Firebird ALWAYS gives me the right couple (F4, F5)?
(maybe, this is related to Firebird's interpretation of FIRST keyword.)
e.g. Talking for first row, it does NOT give y instead of x, right?
Thanks
ASKER
sorry I wanted to increase teh points as well, it didn't.. i'm trying again
muratyaman,
> e.g. Talking for first row, it does NOT give y instead of x, right?
it could give x or y, you cannot rely upon the order that
"Select first 1 F5 from Table2 where table2.F1 = table1.F1"
will get the data and hence you don't know which row will be first
> e.g. Talking for first row, it does NOT give y instead of x, right?
it could give x or y, you cannot rely upon the order that
"Select first 1 F5 from Table2 where table2.F1 = table1.F1"
will get the data and hence you don't know which row will be first
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Or you can write a Store procedure:
set term ^ ;
CREATE PROCEDURE table12
RETURNS
(
F1 integer,
F2 varchar(10),
F3 varchar(10),
F4 varchar(10),
F5 varchar(10)
)
AS
begin
for select F1, F2, F3 from table1 into :F1, :F2, :F3 do
begin
select F4, F5 from table2 where F1 = :F1 into :F4, :F5;
suspend;
end
end
^
set term ; ^
select * from table12;
(once again I don't have firebird on my computer so there are probaly mistakes...)
set term ^ ;
CREATE PROCEDURE table12
RETURNS
(
F1 integer,
F2 varchar(10),
F3 varchar(10),
F4 varchar(10),
F5 varchar(10)
)
AS
begin
for select F1, F2, F3 from table1 into :F1, :F2, :F3 do
begin
select F4, F5 from table2 where F1 = :F1 into :F4, :F5;
suspend;
end
end
^
set term ; ^
select * from table12;
(once again I don't have firebird on my computer so there are probaly mistakes...)