Modified at $Date: 2005/03/09 07:11:12 $
<h2>Can't believe it takes me so long to do a so easy thing in sql.</h2>
<p>
I have to do something to make myself the best. I forgot so much... Luckily I still can write something. ^_^.
</p>
<p>
A working guy beside me want me to help him to write a query.
He want to select fields in a table which has one to many relation in the field in the same table. Suppose
<table border="1">
<tr><td>ClinicAddress</td><td>Clinic</td></tr>
<tr><td>1</td><td>1</td></tr>
<tr><td>2</td><td>1</td></tr>
<tr><td>3</td><td>2</td></tr>
<tr><td>4</td><td>3</td></tr>
<tr><td>5</td><td>4</td></tr>
<tr><td>6</td><td>5</td></tr>
<tr><td>7</td><td>4</td></tr>
</table>
</p>
<p>
select field clinic which has more than one ClinicAddress. One clinic has more than one clinicAddress. How to do this in Query Analyzer.
</p>
<p>
sql query is below.
<pre>
<font color="#66cd00">SELECT</font> clinic, COUNT(*) as countNumber
<font color="#66cd00">FROM</font> clinic_address
<font color="#66cd00">GROUP BY</font> clinic
<font color="#66cd00">HAVING COUNT(*)</font> > 1
<font color="#66cd00">ORDER BY</font> countNumber
</pre>
</p>
<p>
Use <font color="#66cd00"><i>HAVING</i></font> clause to make it. Easy? But why I forgot it???
What a shame to me!
</p>