Sorry if this seems a bit noobish, but it's been giving me a headache for ages.
Basically, I have two tables (that we're interested in).. let's see if I can set out a scenario that's similar, but easy to understand.
let's call the first table field_animal. This will be a count of how many of each animal is in a field (the field_id is a foreign key to another table with data about the field, but that's not of interest to us). animal_id is a foreign key to my second table, and count is the number of each animal in the field. let's have it layout out as such, with some sample data:
<pre>
+----------+-----------+-------+
| field_id | animal_id | count |
+----------+-----------+-------+
|1 |1 |7 |
|1 |2 |3 |
|2 |3 |13 |
|1 |4 |1 |
|2 |1 |6 |
+----------+-----------+-------+
</pre>
Lets also have a table with the animal names
<pre>
+-----------+-------------+
| animal_id | animal_name |
+-----------+-------------+
|1 |Cow |
|2 |Sheep |
|3 |Pig |
|4 |Horse |
|5 |Chicken |
+-----------+-------------+
</pre>
You will note that some animals appear in both fields, some in only one field, and some in neither.
What I want to do is run a query that will display all the animals, *But* Order them by how many are in field 1. E.g. in this order: Cow, Sheep, Horse, Pig, Chicken. (This is for a drop-down box on a website.)
The closest I've tried using a left join, but as soon as I say WHERE field_id=1 I would lose the sheep and chicken off my list. The only solution I have so far is 2 separate queries - one to display Cow, Sheep, Horse and the other to display Pig, Chicken.
Is there a way of doing this in one query?
Thanks in advance.