Join tabels and also getting the one where only exist in one table

[Larshg]

I have 2 tables that I'm juning

Something like this
SELECT TABLE1.FIELD1, TABLE1.FIELD2, TABLE2.FIELD3
FROM TABLE1 INNER JOIN TABLE2 ON TABLE1.FIELD1 = TABLE2.FIELD1

The problem is that I get the result of the 2 tables is only where TABLE1.FIELD1 = TABLE2.FIELD1 and there are some fields where the value in TABLE.FIELD1 does not exists in TABLE2.FIELD1 thise results are also interesting.

In genneral what I'm trying to do is to get a select of TABLE1.FIELD1, TABLE1.FIELD2 matched up with TABLE2.FIELD3 where TABLE1.FIELD1 = TABLE2.FIELD1 and if the TABLE1.FIELD1 does not exists in TABLE2.FIELD1 then TABLE2.FIELD3 should just be empty - BUT STILL DISPLAYED.


Here is an example of the tabels and the result I looking for

TABLE1
---------------------------------------
NEWDELFI HP_UX server
ODIN T64 server
ODIN2 T64 server
-----------------------------------------

TABLE2
---------------------------------------
NEWDELFI HP responsable
ODIN COMPAQ/HP responsable
---------------------------------------

The result og the select should be
--------------------------------------------------
NEWDELFI HP_UX server HP responsable
ODIN T64 server COMPAQ/HP responsable
ODIN2 T64 server 'NULL'


Is this posible or should I make 2 queryes 1 where TABLE1.FIELD1 = TABLE2.FIELD1 and one where TABLE1.FIELD1 not in TABLE2.FIELD1 and then but them together.

/Larshg

 

[spongie1]

Maybe I am not understanding you...

If you make put tbl1 on the left and tbl2 on the right...
then create your joins from tbl1 to tbl2...
then right click the first join line and select "join properties"...
then select option #2 for join type...

If you do that for all of the joins between your two tables... shouldn't that give you what you want?

 

[spongie1]

oh, and make sure that the field you are looking at is the tbl1.field1 or else you will not see what you want to see.

 

[Larshg]

Hi

Yes that is exatcly what I wanted, and I even found an reference to it in the help - in my case I using a LEFT JOIN insted of an INNER JOIN

Thanks ..... problem solvede

/Lars