CODEWITHSUNDEEP
sqloracle-databasejoin
user24772222222222222222222222
user24772222222222222222222222

Reputation: 104

how to get this sql data with a join and non unique key relating two tables

I have two tables:

images
imageid   imageKeySTring
-------   --------------
98        AAA
97        BBB
96        CCC
95        CCC
94        BBB

objects
imageKeyString     id2
--------------     -----
AAA                2
BBB                3
AAA                4
CCC                5
CCC                6
BBB                7

All combinations of id2 with imageid linked by matching imageKeyString how would I get this in an Oracle SQL query?

Example, for CCC I would get back:

5/95
5/96
6/95
6/96

returned (but I want all key returned values in the query)

Upvotes: 1

Views: 236

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269445

You need to join the tables together AND concatenate the results:

select i.imageKeyString,
       cast(o.id2 as varchar(10)||'/'||cast(i.imageid as varchar(10)) as newkey
from images i join
     objects o
     on i.imageKeyString = o.ImageKeyString

If some objects are missing:

select i.imageKeyString,
       coalesce(cast(o.id2 as varchar(10), '')||'/'||cast(i.imageid as varchar(10)) as newkey
from images i left outer join
     objects o
     on i.imageKeyString = o.ImageKeyString

Upvotes: 0

Andomar
Andomar

Reputation: 238048

Looks like a join would do what you ask. It returns all matching rows from the joined table:

select  i.imageid
,       o.id2
from    images i
join    objects o
on      o.imageKeyString = s.ImageKeyString

Upvotes: 3

Related Questions