Help with SQL statement revisited.

jtown

Ok, here's my problem. It relates to a previous solution I got from here but I will write it out again.

Ok say I have one table of animals ... say it's called ANIMALS

ID/Common Name/Species/Category/Etc.

Now I have another table of inventory of these animals in another table .... say it's called ANIMALINVENTORY

ID/AnimalID(filled with ANIMAL.id's)/CageLocation/SeenVet/CheckedInBy/Etc.

Now what I want to do is construct a table ordered in the following manner where all the animals listed were CHECKED IN BY say 'jtown' or any other person.

So say ANIMAL INVENTORY looked like this:

ID | AnimalID | CheckedInBy

1 | 10 | jtown
2 | 10 | charlie
3 | 10 | lisa
4 | 3 | jtown
5 | 10 | ron
6 | 18 | lala
7 | 3 | ron
8 | 99 | whatever
9 | 18 | jtown
10 | 77 | whatever
11 | 55 | whatever

Now I want the outut table to look like this ....

Animal Name | Animal ID | Number of Animals (In ANIMAL INVENTORY)

Dog | 10 | 4
Cat | 3 | 2
Lizard | 18 | 2

Now, this wouldn't be so difficult to do if the CheckedInBy was all 'jtown', but it's not. I hope that makes sense.

My old post is here .... http://www.phpbuilder.com/board/showthread.php?s=&threadid=10228035

Norman_Graham

I think I don't understand - according to what you've written here, there are several animals with the same ID. This makes no sense to me. Surely the ID of each animal is unique. You've got four dogs with ID no. 10, two cats with ID no. 3 and two lizards with ID no. 18 - or at least that's what your desired output table shows. Also your animal name appears to be 'dog' or 'cat', as opposed to 'Rover' and 'Miffy'. Did you mean species rather than name? Your table headings don't match up and are confusing me, I'm afraid.

Can you perhaps explain more clearly what your problem is and how your data works?

Thanx

Norman

nnichols

You can use this -

SELECT CommonName, Species, COUNT(ANIMALINVENTORY.AnimalID) AS num 
FROM ANIMALS 
LEFT JOIN ANIMALINVENTORY 
ON ANIMALS.ID = ANIMALINVENTORY.AnimalID 
WHERE ANIMALS.TYPE = 'BIG' AND ANIMALINVENTORY.CheckedInBy = 'jtown'
GROUP BY ANIMALS.ID 
ORDER BY num DESC

but this won't give you the 0s

Hope this helps 😉

jtown

Originally posted by Norman Graham
I think I don't understand - according to what you've written here, there are several animals with the same ID. This makes no sense to me. Surely the ID of each animal is unique. You've got four dogs with ID no. 10, two cats with ID no. 3 and two lizards with ID no. 18 - or at least that's what your desired output table shows. Also your animal name appears to be 'dog' or 'cat', as opposed to 'Rover' and 'Miffy'. Did you mean species rather than name? Your table headings don't match up and are confusing me, I'm afraid.

Can you perhaps explain more clearly what your problem is and how your data works?

Thanx

Norman

the multiple id's is because that's the Inventory DB not the ANIMALS db ... the ANIMALS DB would be what you are talking about.

jtown

Originally posted by nnichols
You can use this -

SELECT CommonName, Species, COUNT(ANIMALINVENTORY.AnimalID) AS num 
FROM ANIMALS 
LEFT JOIN ANIMALINVENTORY 
ON ANIMALS.ID = ANIMALINVENTORY.AnimalID 
WHERE ANIMALS.TYPE = 'BIG' AND ANIMALINVENTORY.CheckedInBy = 'jtown'
GROUP BY ANIMALS.ID 
ORDER BY num DESC

but this won't give you the 0s

Hope this helps 😉 [/B]

edited .... see below.

jtown

the problem with this query is that I want to count the inventory that isn't entered by 'jtown' also.

right now it's not working because it's returning num as 1 for each animal that 'jtown' has entered when I want it to count the number of current animals entered by 'jtown' as well as other people (but only those animals jtown has entered)

nnichols

I can't think of anyway you can do this in a single query. Try this -

$sql = "SELECT DISTINCT AnimalID
FROM ANIMALINVENTORY
WHERE CheckedInBy = 'jtown'";

$res = mysql_query($sql, $db) or die('MySQL error: ' . mysql_error() . '<br>Query: ' . $sql);

while ($row=mysql_fetch_row($res)) {
$temp[] = $row[0];
}
$AnimIDs = implode(', ', $temp);

$sql = "SELECT CommonName, Species, COUNT(ANIMALINVENTORY.AnimalID) AS num 
FROM ANIMALS 
LEFT JOIN ANIMALINVENTORY 
ON ANIMALS.ID = ANIMALINVENTORY.AnimalID 
WHERE ANIMALS.TYPE = 'BIG' AND ANIMALS.ID IN ($AnimIDs)
GROUP BY ANIMALS.ID 
ORDER BY num DESC";

Hope this helps 😉

jtown

Originally posted by nnichols
I can't think of anyway you can do this in a single query. Try this -

$sql = "SELECT DISTINCT AnimalID
FROM ANIMALINVENTORY
WHERE CheckedInBy = 'jtown'";

$res = mysql_query($sql, $db) or die('MySQL error: ' . mysql_error() . '<br>Query: ' . $sql);

while ($row=mysql_fetch_row($res)) {
$temp[] = $row[0];
}
$AnimIDs = implode(', ', $temp);

$sql = "SELECT CommonName, Species, COUNT(ANIMALINVENTORY.AnimalID) AS num 
FROM ANIMALS 
LEFT JOIN ANIMALINVENTORY 
ON ANIMALS.ID = ANIMALINVENTORY.AnimalID 
WHERE ANIMALS.TYPE = 'BIG' AND ANIMALS.ID IN ($AnimIDs)
GROUP BY ANIMALS.ID 
ORDER BY num DESC";

Hope this helps 😉 [/B]

you are the MAN!

that's what I'm lookin' for. thanks again!