Oracle query

ok I am through pulling my hair out and decided to let you guys help me .

I have 2 tables
Table 1
ID - Container - internalid
100 A01001 1
101 A01002 3
102 A01003 2
103 A01001 4
104 A01003 5

Table 2
ID Container ShortName
1 A01001 A01
2 A01002 A01
3 A01003 A01

My problem is this
1 - I start with the internal id of 1
2 - I need ot find the only container in table 1 with 1 record

Expected output = 102

My attempts have been
select
id, container
from
table 1
where
substr(container,1,9) in
(
select substr(container,1,9)
from table 1
where ID in (1)
) and
having count(container) = 1
group by id, container -- I really don't want to group here, but have to and this screws up the query
order by container


Thanks in advance

I'm not sure why you need table2 at all, so maybe I didn't understand your question completely.

But this returns those rows where there is only one container:

shammat,

Thanks for the response. I did not give a good sample dataset for the question.

Table 1
ID - Container - internalid
100 A01001 1
101 A01002 3
102 A01003 2
103 A01001 4
104 A01003 5
105 A01004 6
106 A02001 7
107 A02001 8
108 A02002 9
109 A02003 10

Now the query that you gave will return
A01002, A01004 A02002 and A02003. I need to return only one value per substr(container,1,3).
so we could get either A01002 or A01004. (it doesn't matter which one.)

Since table 2 has the A01, A02,A03 values, I thought it would be eaiser to use that table aid in the joins.

If I try to do a filter on substr(container,1,3) I end up with what I need, however once I group on the container, it reverts back to the all the values per container.

Hi,

I created a table (table_1) with the following data as posted in your first question:
100 A01001 1
101 A01002 3
102 A01003 2
103 A01001 4
104 A01003 5

If I have understood your question correctly then you wish to find the container having a unique value in table_1 , i.e no other row has that container value. So as per the data above, that row should be the one where internal id is 3. Because the container value of A01002 is not present in other rows. Please see my query below:

select id , container,internalid from table_1 where container in (
select t1.container
from table_1 t1
group by t1.container
having count(t1.container) <= 1);