I've tried to get this for about 2 hours without success. Here is an example of what I need to do:
Tables
people:
nameA score
---------------------
someone1 24
someone2 24
someone3 24
someone4 23
someone5 21
someone6 24
someone7 19
someone8 20
someone9 24
someone10 24
runs:
nameB add
---------------------
someone1 s
someone2
someone2
someone4 s
someone5
someone4
someone7 s
someone8 s
someone7
someone7 s
Please note, this is only a example to visually show my problem. No need to talk about the setting of my tables.
What I want to know:
First of all I need all entries from table people where the score is smaller than 24 (easy till here).
Next step is to only show results where people.name has no s in table runs (my actual problem).
Output should be:
name score
---------------------
someone2 22 /* no "s"-entry in runs */
someone5 21 /* also no entries */
Unfortunately im not very fimiliar with joins. Here is my last try:
SELECT nameA,score FROM runs,people WHERE people.nameA = runs.nameB
AND people.score < 24
AND runs.add != "s"
GROUP BY people.nameA
In pseudo-code:
foreach Person in (SELECT nameA as Person,score FROM people WHERE score < 24)
SHOW name.B = Person WHERE add != "s"
I know this is shit :). But it describes what I need in 2 lines.
I would appreciate any help here.
Thanks!
select * from people where score<24 and
name in (select name from runs where add!=s) and
name not in (select name from runs where add=s)