help with mysql select query

Question

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     started

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

You should show what you have tried already to show what you are having trouble with — Alan Whitelaw
– Alan Whitelaw, Commented Feb 6, 2011 at 8:45
i already tried something like : select * from log where log.state = started and log.state !=drop out but it didn't work the way i want it because it would show "ali" as started even though he is not anymore (drop out) so what i want is something to show people who started and still starting (didn't drop out) and oh the drop out date is always after the start date but i omit the day for simplicy as i can simply "group by" and add "having" the date between the 2 dates — Ali
– Ali, Commented Feb 6, 2011 at 16:20

Alan Whitelaw · Accepted Answer · 2011-02-06 08:49:22Z

3

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;

answered Feb 6, 2011 at 8:49

Alan Whitelaw

17k9 gold badges37 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Ali Over a year ago

thanks for your reply but i want my results filtered based on the state not the names

Alan Whitelaw Over a year ago

Sorry your question was unclear, this is what you need: SELECT * FROM table WHERE state != 'drop out' GROUP BY name; This will only show one row per name, and no one who has dropped out.

Alan Whitelaw Over a year ago

No it wouldn't! GROUP will only show people (once) who have not dropped out. Try it!

Ali Over a year ago

yes but what if someone registered and didn't start, the query only exclude the drop out am i right?

Brian Hooper · Accepted Answer · 2011-02-06 09:18:12Z

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.

answered Feb 6, 2011 at 9:18

Brian Hooper

22.2k25 gold badges91 silver badges146 bronze badges

8 Comments

Ali Over a year ago

thank you i think this would solve it although i need to try it first, will it work in MS Access ? sorry for my laziness

Brian Hooper Over a year ago

@Ali, I don't see any reason why it wouldn't.

Ali Over a year ago

it worked but it's taking forever to show the results...:( why is that?

Brian Hooper Over a year ago

@Ali, if you are using MySQL, you could try EXPLAIN ..., and post the results; otherwise I'd guess you need an index on the NAME and STATE columns, but without the explain results, that is a guess. How large is your table?

Ali Over a year ago

SELECT DISTINCTROW Basic.ApplicationNumber FROM Service INNER JOIN (Basic INNER JOIN Log ON Basic.[b_id] = Log.[b_fid]) ON Service.[service_id] = Log.[s_fid] WHERE (((Format$([ServiceDate],'mmmm yyyy'))="January 2011") AND ((Service.ServiceName)="english") AND ((Log.state)=1) AND ((Exists (

|

chustar · Accepted Answer · 2011-02-06 20:32:47Z

1

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.

edited Feb 6, 2011 at 20:32

answered Feb 6, 2011 at 8:41

chustar

12.5k26 gold badges85 silver badges119 bronze badges

2 Comments

Ali Over a year ago

thanks for your reply that would show Ali who started but then droped out while what i needed only people who started and didn't drop out

Ali Over a year ago

SELECT Basic.ApplicationNumber,log.state FROM Service INNER JOIN (Basic INNER JOIN Log ON Basic.b_id = Log.b_fid) ON Service.service_id = Log.s_fid WHERE (Service.ServiceName="english" AND Format$(log.ServiceDate,'mmmm yyyy')="January 2011") GROUP BY Basic.ApplicationNumber,log.state having log.state=1 it didn't work it shows all the started and it does not exclude the drop outs

S L · Accepted Answer · 2011-02-06 08:53:29Z

0

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

edited Feb 6, 2011 at 8:53

S L

14.3k18 gold badges81 silver badges119 bronze badges

answered Feb 6, 2011 at 8:34

Jinah Adam

1,1252 gold badges14 silver badges27 bronze badges

2 Comments

Alan Whitelaw Over a year ago

-1 that would include the registered row too. (should your sql say drop out)

Ali Over a year ago

exactly :) i don't want to show the registered i only need the started who didn't drop out

Collectives™ on Stack Overflow

help with mysql select query

4 Answers 4

4 Comments

8 Comments

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

8 Comments

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related