-1

New to the site but I have a snapshot of a query that doesn't work and I can't get my head around why.

type 5 is a call, type 0 is a note (but could be a call due to record keeping) so I'm trying to find = type 5 or (type 0 where the clients name is within the note).

Below is the section that fails

AND (ct.tpe = 5 OR (ct.tpe = 0 AND ct.smmry LIKE ('%', c.frstnme1 ,'%')))

This works if I replace c.frstnme1 with the actual name in that field, but it won't work with the request for the information from c.frstnme1. I don't get this as there can only be one item of info in c.frstnme1 and the table is already linked as "FROM c, ct WHERE ct.cse = c.id" so it shouldn't be trying to find other first names. But again it works with the exact same query but with the actual name typed. Any help please!

Thanks Will

Martin
  • 22,212
  • 11
  • 70
  • 132
Will
  • 1
  • 3
  • That quote's the error message or your specs? – Álvaro González Oct 16 '15 at 11:57
  • Error messages normally give the reason and the exact location within the code. The fact that you don't understand a given error message doesn't imply that message is useless! – Álvaro González Oct 16 '15 at 15:26
  • I didn't say that it was useless, I said I couldn't get my head around it and asked for help. This has been resolved now anyway. Thanks! – Will Oct 16 '15 at 15:31
  • Well, you didn't think it was relevant enough to include it in the question. Whoever has the same problem in the future is unlikely to google here ;-) – Álvaro González Oct 17 '15 at 13:24

3 Answers3

2

You are missing concat() for the like pattern:

AND (ct.tpe = 5 OR (ct.tpe = 0 AND ct.smmry LIKE CONCAT('%', c.frstnme1, '%')))

The strange error arises because , is not allowed in the context where you have it.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Perfect, thank you! I'll do some more research on the CONCAT command so I don't miss it out in future. – Will Oct 16 '15 at 11:53
1

What you need to do is use the MySQL Concat function inside your query. 

AND (ct.tpe = 5 OR (ct.tpe = 0 AND ct.smmry LIKE CONCAT('%', c.frstnme1 ,'%')))

It is failing because you are not specifying that you are concatenating strings with a column. It works when you replace it with the actual name because you are then making the query: 

AND (ct.tpe = 5 OR (ct.tpe = 0 AND ct.smmry LIKE ('%ACTUAL NAME%')))
Caleb Hess
  • 36
  • 5
  • Thank you, Gordon beat you to it but your reply is appreciated. I'll do some reading on CONCAT as my pea brain doesn't understand it's function but I'll make sure it does asap. Thanks! – Will Oct 16 '15 at 12:07
0

when ever we are using a like in condition,we need to keep the comparison string or some thing in "concat" function.

Thanks.

Phani
  • 93
  • 1
  • 8