selection output is sorted incorrectly
I am trying to display data on people sorted by last name.
Private people should be displayed at the bottom of the list.
For some reason, the data is displayed out of order.
Here are my tables:
People: # keeps info on people
Privacy varchar (255) # either 'enabled' or 'enabled'
sorted_people: # holds the Ids of the people sorted according to different critirias (lastname...)
The By_Lastname column holds the IDs of all the people, already sorted by lastname
privacy_settings: # what data should be private
shouldPrivatizeLastName varchar(255) # 'yes' or 'no'
I would like to display all the IDs and last names of the people sorted like so:
If the last name is not private, display it in the order defined in the sorted_people table. Otherwise, display it last.
Here is my query:
select sorted_people.By_Lastname, people.Last_name,
from people, privacy_settings, sorted_people
where (sorted_people.By_Lastname = people.ID)
order by case when (privacy_settings.shouldPrivatizeLastName = 'no' OR people.Privacy = 'disabled') then 1 else 2 end;
I am using (sorted_people.By_Lastname = people.ID) to connect between the two tables
I am using when (privacy_settings.shouldPrivatizeLastName = 'no' OR people.Privacy = 'disabled') then 1 else 2
to place all the people at the top or at the bottom of the list.
does someone see what causes the list not to be sorted according to what I am trying to achieve?
thanks in advance