CODEWITHSUNDEEP
sqloracle-database
RNJ
RNJ

Reputation: 15552

Using a parameter from another query in a like clause

I am trying to do a join and where column using an inner query. the actual query is a bit more complicated that the below but I have simplified it for this questiion. Consider I have a peopel table. I have in it two likes. The first name is George in both rows but the last_name is Lloyd and Lloyds in each row. I want to be able to return both rows. I am looking for Lloyd - returning that in the inner join and then trying to do a like int he outside query. This only returns the first row though with Lloyd. not the one with the s.

Is it possible to do this? If I was looking for Lloyd and hardcoding it I could do 

like 'Lloyd%'

but in my case there are many rows Im returning so I want to do it dynamically.

select * from people p
join
 (select p1.id, p1.first_name, p1.last_name 
  from people p1 
  where p1.first_name = 'George' and p1.last_name = 'Lloyd'     and p1.id = 17)
un on un.id = p.id
where p.first_name = un.first_name and p.last_name like  '%' || un.last_name || '%'

Thanks

Upvotes: 0

Views: 67

Answers (3)

sstan
sstan

Reputation: 36513

Because your join condition is on un.id = p.id, your result set is always restricted to id = 17.

It sounds like this is the query you were trying to write:

select * from people p
join
 (select p1.id, p1.first_name, p1.last_name 
  from people p1 
  where p1.first_name = 'George' and p1.last_name = 'Lloyd'     and p1.id = 17)
un on p.first_name = un.first_name and p.last_name like  '%' || un.last_name || '%'

... which I would personally clean up a little to this:

select p2.*
  from people p1
  join people p2
    on p2.first_name = p1.first_name
   and p2.last_name like '%' || p1.last_name || '%'
 where p1.first_name = 'George'
   and p1.last_name = 'Lloyd'
   and p1.id = 17

Upvotes: 1

Cristian_I
Cristian_I

Reputation: 1625

You can try this:

SELECT * FROM PEOPLE P
INNER JOIN PEOPLE P2 ON P.FIRST_NAME = P2.FIRST_NAME 
and instr(upper(p.last_name),upper(p2.last_name))<>0;

Upvotes: 1

knuckles
knuckles

Reputation: 101

You are only receiving one row because your query is predicated on just one row (assuming id is unique in your table)

Remove the id = 17 

    select * 
    from people
    where first_name = :first_name
    and last_name like :last_name

Upvotes: 1

Related Questions