JOIN SQL Having , MYSQL

[aseel_1]

Suppose I have a tow SQL tables:
Fathers

id  |  name    
-------------  
1   | Clinton
2   | Gates
3   | Bush
4   | Clinton

Sons

id | fatherid | name
---------------------
1  |  1       | bill
2  |  1       | suzy
3  |  2       | mera
4  |  2       | suzy 
5  |  3       | bill
6  |  3       | rose
7  |  4       | bill

** I would like to write a query that returns the Fathers who have (at least two sons) one of sons named bill and the other son is not suzy . How do I write this SQL query?**

[tapananand]

Select F.name from Fathers F, Sons S
where F.id = S.fatherid
and
S.name='bill'
and
S.fatherid in (select S2.fatherid from Sons S2 group by S2.fatherid having count(*) >= 2)
and
S.fatherid not in (Select S.fatherid from Sons S where S.name='suzy');

I guess this will do.

[filipgothic]

      $sql = mysql_query ("SELECT fatherid from Sons WHERE name = 'bill' AND <> 'suzy'");

    $fathers = mysql_fetch_array($sql);

      $selection = mysql_query("select name from Fathers where id = ".$fathers['fatherid']."");


    echo "Fathers: ";
        while($row = mysql_fetch_array($selection))
      {

        echo "<br>".$row['name'];
        echo "<br>".$row['name'];
        .
        .
        .

  }

I think this will work for one part

and for other part where is needed number 2 or more sons, am not sure atm, but someone will give you answer soon I am sure

[tapananand]

@filipgothic: your first query is also wrong, when name is bill it will surely not be suzy so all fathers who have bill as one of his son will get selected.

[filipgothic]

oh okay then, I did not saw someone else posted solution also, I wouldnt post then, mine is out then

but I thought all bill's should be selected anyways

P.S aha I got it, it will select only bill's name, and not others