Query with number OR text

[jtelo]

Hi!

 

I've a table 'books' with this data:

yeardiscipline

pré-escolargeometry

1math

1english

 

I'm trying to do a mysql query to return the distinct disciplines with  the year 'pré-escolar' and '1'. I've tried with

SELECT distinct `discipline` FROM `books` WHERE `year`="Pré-escolar" OR `year`="1"

 

But it only returns 'geometry'.

 

Can someone help me? Thx.

[ebmigue]

Why not

SELECT distinct `discipline` FROM `books` WHERE `year`='Pré-escolar' OR `year`='1'

?

 

If that will not work, try:

 

SELECT distinct `discipline` FROM `books` WHERE `year`='Pré-escolar' 
UNION
SELECT distinct `discipline` FROM `books` WHERE  `year`='1'

 

But really, using the WHERE ...OR.. version should suffice. But the two queries are logically equivalent, so either of them should do.

 

For current MySQL implementation, maybe the first version will fair well, performance wise.

[jtelo]

Thank you for the quick reply, but i'm getting the same result with both of your suggestions.

 

I've more rows on that table with more years. If i make this query only with numbers, it returns the disciplines of both years.

SELECT distinct `discipline` FROM `books` WHERE `year`='1' OR `year`='2'

 

But with 'pré-escolar' and a number it doesn't work.

[jtelo]

MySQL version: 5.1.4.5

[jtelo]

discovered the problem. The numbers had quotes in numbers. The correct syntax is the following:

 

SELECT distinct `discipline` FROM `books` WHERE `year`=1 OR `year`="Pré-escolar"

[fenway]

I can't believe that's actually the problem -- there is little harm in quoting literals.

[jtelo]

I'm not an expert in this matter, but the query only returns the proper results this way.

 

SELECT distinct `discipline` FROM `books` WHERE `year`="1" OR `year`="Pré-escolar"

Only returns the discipline of of 'pré-escolar'.

 

SELECT distinct `discipline` FROM `books` WHERE `year`=1 OR `year`=Pré-escolar

Shows an SQL error: #1054 - Unknown column 'Pré' in 'where clause'

 

SELECT distinct `discipline` FROM `books` WHERE `year`=1 OR `year`="Pré-escolar"

Works!

[fenway]

In the query browser, or via php?