need to select where table.col like table2.col2

[dangari]

Hi all,

Am having trouble trying to retrieve records using the following SQL statement:

SELECT DISTINCT(dbS.tCline.mobile),dbS.tCline.msg,dbS.tCline.dest,dbS.tCline.reply,dbS.tCline.statusFROM dbS.tCline,dbLaw.FORMER_GREYWHERE dbS.tCline.msg LIKE '%'||dbLaw.FORMER_GREY.C||'%' ANDdbS.tCline.msg LIKE '%ADMISSION%'SELECT distinct(dbS.tCline.mobile),dbS.tCline.msg,dbS.tCline.dest,
dbS.tCline.reply,dbS.tCline.status
FROM dbS.tCline,dbLaw.FORMER_GREY
where dbS.tCline.msg LIKE '%'||dbLaw.FORMER_GREY.C||'%' and
dbS.tCline.msg like '%ADMISSION%'

My problem now is that, the resultset retrieved includes everything from dbLaw.FORMER_GREY.C instead of only those rows that match dbS.tCline.msg rows.Please assist and be sure that your help is much appreciated.
Thank you in advance

 

[r937]

would you please try pasting your query again, there are serious syntax errors with what you've posted

also, please confirm which version of mysql you're using

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[dangari]

Thanks for looking at my post.The syntax I have posted is spot-on except for the area where I need help with where dbS.tCline.msg LIKE '%'||dbLaw.FORMER_GREY.C||'%'
Please note that this is what someone posted as a solution in a certain Oracle blog but didn't work for me in MySQL.

The version of MySQL that am using is 5.1.37. Thanks

 

[dangari]

Sorry it seems I must pasted the code twice..Here it is :

SELECT DISTINCT(dbS.tCline.mobile),dbS.tCline.msg,dbS.tCline.dest,dbS.tCline.reply,dbS.tCline.status FROM dbS.tCline,dbLaw.FORMER_GREY WHERE dbS.tCline.msg LIKE '%'||dbLaw.FORMER_GREY.C||'%' AND dbS.tCline.msg LIKE '%ADMISSION%

The syntax above is spot-on except for the area where I need help with where dbS.tCline.msg LIKE '%'||dbLaw.FORMER_GREY.C||'%'
Please note that this is what someone else posted as a solution in a certain Oracle blog but didn't work for me in MySQL.

The version of MySQL that am using is 5.1.37. Thanks

 

[dangari]

Here is the full-proof solution:

SELECT distinct(s.mobile),s.msg,r.imei,s.dest,
s.reply,s.status from dbS.tCline s
INNER JOIN dbLaw.FORMER_GREY r ON s.msg LIKE concat('%',concat(r.C,'%')) where s.msg like '%SOLD%'

Many thanks!!

 

[r937]

concat('%',concat(r.C,'%')) can be simplified to [red]concat('%',r.C,'%')[/red]

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[dangari]

Thanks for the insight.