WHERE LIKE not displaying correct results

Question

I have a WHERE LIKE clause in my query that is not displaying the correct results.

My query is as follows;

SELECT book.isbn, book.title, 
copy.code, copy.duration,
student.no, student.name
FROM book
INNER JOIN copy
ON copy.isbn = book.isbn
INNER JOIN loan
ON loan.code = copy.code
INNER JOIN student
ON student.no = loan.no
WHERE copy.code LIKE 1013 OR 3011

I have tried inserting % but I am given an error. At the moment, all results containing any combination of the integers is displayed. I am looking to only display results where 'code' equals 1013 and 3011 exactly.

depending on which database specifically you're using, you MIGHT be able to get away with going `where copy.code like '%1013%' or copy.code like '%3011%'` After re-reading, it looks like you're probably looking for an in condition as has been described by others though...using `like` is for pattern matching within a string, using an `in` statement or multiple equals statements is for exact matches. Personally i prefer using `in` statements as it's considerably easier to read (especially once you end up with 3 or more conditions to match on) — user2366842, Feb 03 '15 at 14:40

score 2 · Answer 1 · answered Feb 03 '15 at 14:27

2

For exact matching use IN

WHERE copy.code in (1013, 3011)

or =

WHERE copy.code = 1013 or copy.code = 3011

answered Feb 03 '15 at 14:27

juergen d

201,996
37
293
362

score 1 · Answer 2 · answered Feb 03 '15 at 14:32

Try this way, note LIKE is for strings values not for numbers

SELECT book.isbn, book.title, 
  copy.code, copy.duration,
  student.no, student.name
FROM book
 INNER JOIN copy
ON copy.isbn = book.isbn
 INNER JOIN loan
ON loan.code = copy.code
 INNER JOIN student
ON student.no = loan.no
 WHERE copy.code=1013 OR copy.code=3011

tonirush · Answer 3 · 2015-02-03T14:40:04.830

0

If copy.code is a varchar you can use one of the following lines

WHERE copy.code LIKE '1013' OR copy.code LIKE '3011'
WHERE copy.code = '1013' OR copy.code = '3011'
WHERE copy.code in('1013','3011')

if copy.code is a number you can use one of these

WHERE copy.code = 1013 OR copy.code = 3011
WHERE copy.code in (1013,3011)

edited Feb 03 '15 at 14:40

answered Feb 03 '15 at 14:31

tonirush

430
1
6
14

should be noted that at least in some databases (MS's flavor of SQL for example) you can still optionally throw single quotes around an int value and it will match. Unfortunately the original poster didn't specify which database they were using... – user2366842 Feb 03 '15 at 14:43

score 0 · Answer 4 · answered Feb 03 '15 at 14:32

Besides the correct observation in the comments that LIKE is for text, not numbers, an important thing to know is that when you use OR in SQL (or any programming language), you have to specify the entire other condition, not just the other value:

WHERE copy.code = 1013 OR copy.code = 3011

So you mention the copy.code = part twice, otherwise the computer doesn't understand what you mean with just 3011.

WHERE LIKE not displaying correct results

4 Answers4