help with mysql select query

what I'm trying to do:

from these tables

---------------------------------
name      date      state
---------------------------------
ali      jan 12    started

ali      jan 12    drop out

masa     jan 12    registered

masa     jan 12    started

sami     jan 12    started

I want the results to be

---------------------------------
name      date      state
---------------------------------
masa     jan 12     start开发者_如何学编程ed

sami     jan 12     started

So basically what i want is to have all the started users without the ones who dropped out so the filtering should be based on the state thanks

Those two rows in your result example are not the only started people on that date.

SELECT * FROM table WHERE state = 'started';

Would return 3 rows:

---------------------------------
name      date      state
---------------------------------

ali      jan 12     started

masa     jan 12     started

sami     jan 12     started

To get the two rows in your example you need:

SELECT * FROM table WHERE name IN ('sami', 'mesa');

Update, added this example

You could limit if you only wanted two rows:

SELECT * FROM table WHERE state = 'started' LIMIT 2;

Perhaps something along the following lines is what you need...

SELECT NAME,
       DATE,
       STATE
   FROM MYTAB
   WHERE STATE = 'Started' AND
         NOT EXISTS (SELECT *
                         FROM MYTAB MYTAB2
                         WHERE MYTAB2.NAME  = MYTAB.NAME AND
                               MYTAB2.STATE = 'Drop Out');

That should get everyone who started and didn't drop out.

SELECT name, date, state FROM table GROUP BY name HAVING state = 'started';

I believe this would eliminate people who dropped out eventually because the GROUP BY would put the people with the same name into the same group, and then eliminate them once they drop out with the HAVING statement.

  select * from table where state != 'dropped out'