getting data in oracle from 2 tables where no entries are guanranteed in one or both tables

Question

I'll simplify my existing example. lets say I have

table1: with ID1, Car; ID2, Bus

and also

table2: with ID1, red; ID3, green

now I want to get everything for ID1 which is simple:

select * from table1, table2 WHERE table1.ID = table2.ID AND table1.ID = 1

and I get ID1, Car, red.

but if I ask for ID = 2

I get nothing because of the missing entry in table2 for ID2. But I want all data for ID2 (and also ID3) even if it has just data in 1 table.

I tried a FULL JOIN which gives me

ID2, Bus

But when I am trying to read those things in C#, I get the datareader has no rows, even while getting the answer in Toad for Oracle.

Full join is ok, and the result in Toad is a confirmation. Your C# code is wrong. — armagedescu, Oct 06 '20 at 08:34
Does this help? https://stackoverflow.com/questions/53949197/isnt-sql-a-left-join-b-just-a/53949327 — Klamsi, Oct 06 '20 at 14:17

score 0 · Answer 1 · answered Oct 05 '20 at 14:56

0

You seem to want a left join:

select *
from table1 t1 
left join table2 t2 on t1.ID = t2.ID

answered Oct 05 '20 at 14:56

GMB

I think you need a full outer join. See my comment above for a visualization – Klamsi Oct 06 '20 at 14:24

1 Answers1