CODEWITHSUNDEEP
mysqlsql
Lukas
Lukas

Reputation: 620

Mysql SELECT priority

My database is missing some values:

Name   Surname    Country   Salary

John   Walker     USA       800
       Walker     Canada    1000
Peter  Walker     Canada    800
John   Walker               900
Peter  Farmer     USA       1200
...    ...        ...

I want to SELECT Name and Surname and Country, if there are no values, I want to SELECT by Name and Country, if there are no values, I want to SELECT by Surname and Country.

I am using now:

SELECT Salary FROM table_name
WHERE (Name='InputFromPHP' AND Surname='InputFromPHP' AND Country='InputFromPHP')
OR (Name='InputFromPHP' AND Surname='InputFromPHP')
OR (Name='InputFromPHP' AND Country='InputFromPHP')

I want to give priority, so it will select exactly what was Input With PHP, but when there are several values in database it gives SQL error.

Thanks a million in advance.

Upvotes: 2

Views: 1667

Answers (1)

trincot
trincot

Reputation: 350137

You could use order by and limit:

SELECT   Salary 
FROM     table_name
WHERE    Name='InputFromPHP'
  AND    (Surname='InputFromPHP' OR Country='InputFromPHP')
ORDER BY CASE Surname WHEN 'InputFromPHP' THEN 0 ELSE 1 END, 
         CASE Country WHEN 'InputFromPHP' THEN 0 ELSE 1 END
LIMIT    1

Note that your where clause always required Name='InputFromPHP', so that can be taken out of the or clause.

Upvotes: 5

Related Questions