Lukas Lukas - 2 months ago 12
MySQL Question

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.

Answer

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.

Comments