-4

i have a table into the database named as country which contains the university name, stream, entrance exam and cutoff. i want to select the universities from tabel and for that i'm using the query as,

SELECT university_name FROM country WHERE stream = ms AND entrance_exam = GMAT OR MS;

i want university_name from table where stream is ms when i select GMAT OR MS in dropdown. please solve my problem and tell how to do that? enter image description here

krishna amarghade
  • 31
  • 9

1 Answers1

1

you can't put OR right after an = test. If you want to test a single column against multiple values, you need to repeat the test, e.g.

stream = 'ms' OR stream = 'mba'

In SQL there's a shortcut for this:

stream IN ('ms', 'mba')

However, this won't work for your design, because you have a comma-separated list. To test for membership, you need to use FIND_IN_SET(), and there's no shortcut for multiple tests.

SELECT university_name
FROM country
WHERE (FIND_IN_SET('ms', stream) OR FIND_IN_SET('mba', stream))
AND (FIND_IN_SET('GMAT', entrance_exam) OR FIND_IN_SET('MS', entrance_exam))

See Query with multiple values in a column

Make sure you put parenthese around each OR expression when they're mixed with AND. See SQL statement is ignoring where parameter

It would be much better if you normalized the data instead of using comma-separated lists. You should have a university_stream table with rows like:

university_name     stream
pune university     ms
pune university     mba
Acadia University   ms
Acadia University   mba

and a university_entrance table with rows like:

university_name     exam
pune university     GMAT
pune university     MS
Acadia University   GMAT
Acadia University   MS

Then you would join the tables:

SELECT DISTINCT a.university_name
FROM university_stream AS a
JOIN university_entrance AS b ON a.university_name = b.university_name
WHERE a.stream IN ('ms', 'mba')
AND b.exam IN ('GMAT', 'MS')
Barmar
  • 741,623
  • 53
  • 500
  • 612