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?**

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.

      $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

@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.

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.