Help with a query

[sqluser2006]

Hello,

This is my table:

firstname | lastname | id | referID

I need to be able to query the table and find all records where lastname field contains a part of some other firstname field and also both the records referID is the same.

For example:

firstname | lastname | id | referID

john ----- smith ------- 5 ----- 25
joe ------ robinson ----- 2 ----- 32
nothing ----- johnsmith ---- 3 --- 25

Here you can see the first record and the third record should be returned as the firstname "john" appears in the lastname of the third record and the referID is the same.

Any help will be appreciated! I have tried and tried but can't seem to figure it out.

Thanks!

[chazzy]

SELECT * FROM your_table a, your_table b WHERE b.lastname like CONCAT('%',a.firstname,'%') AND a.referID = b.referID;

[sqluser2006]

Thanks chazzy... CONCAT doesnt work with sql server does it?

Also, I tried just doing lname=fname to test. It is comparing it with itself and showing as a result as well.

[sridhar_423]

try this ..

SELECT * FROM your_table a, your_table b WHERE (b.lastname like '%'||a.firstname||'%' OR a.firstname like '%'||b.lastname||'%')AND a.referID = b.referID;

john ----- smith ------- 5 ----- 25
nothing ----- johnsmith ---- 3 --- 25


remove the "OR" part if you dont want to display this type of records.
johnsmith ----- smith ------- 5 ----- 25
nothing ----- john ---- 3 --- 25

[sqluser2006]

ok, got it!

Working perfectly.

a.lname LIKE '%' + b.fName + '%' AND a.referID = b.referID

Thanks for your help :)

[chazzy]

Thanks chazzy... CONCAT doesnt work with sql server does it?

Also, I tried just doing lname=fname to test. It is comparing it with itself and showing as a result as well.

If you don't specify what platform you're using, we can't give you platform specific solutions.

can you clarify what you're saying the last one? your original post seemed to say that only lastname can be in firstname, not the other way around.