CODEWITHSUNDEEP
sqlwhere-clausesql-likeclause
Yaz
Yaz

Reputation: 55

WHERE LIKE not displaying correct results

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.

Upvotes: 0

Views: 69

Answers (4)

tonirush
tonirush

Reputation: 430

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)

Upvotes: 0

asontu
asontu

Reputation: 4639

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.

Upvotes: 0

A l w a y s S u n n y
A l w a y s S u n n y

Reputation: 38502

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

Upvotes: 1

juergen d
juergen d

Reputation: 204746

For exact matching use IN

WHERE copy.code in (1013, 3011)

or =

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

Upvotes: 2

Related Questions