Query not working

[yandoo]

Hi there,

 

Im trying to run this query:

 

SELECT BreedID FROM breed WHERE breed.AnimalTypeID = animal.AnimalTypeID

 

Basically the outcome is that the BreedID's records are displayed where the breed.animaltypeid is the same as animal.animaltype.id

 

i can an error saying: "#1054 - Unknown column 'animal.AnimalTypeID' in 'where clause' "

 

 

I started playing around with it my making few changes:

 

SELECT BreedID FROM breed, animal WHERE breed.AnimalTypeID = animal.AnimalTypeID

 

This results in this error message: "#1052 - Column 'BreedID' in field list is ambiguous"

 

 

What am i doing wrong?? Why isnt the query working???

 

Please help

 

Thank You

 

 

 

[GingerRobot]

If a field exists in both tables, you need to specify the table prefix in the query:

 

SELECT breed.BreedID FROM breed, animal WHERE breed.AnimalTypeID = animal.AnimalTypeID 

[yandoo]

Hi there,

 

Thank you for the reply, Yes i see what you mean your code works very well.

 

This however still doesnt give me the results i need because the query NEEDS not only compare the breed.animaltype with animal.AnimalTypeID But it also MUST ensure the RELEVANT animal record  is compared.

 

The results at the moment will display all of the breeds because it is queryng all records in the Animal table and not just the relevant animal record!

 

How can i do this???

 

 

To try and overcome this problem i need to some how add a conditional somewhere to the query so it only brings up ONLY the most recent Animal record.....

 

Then knowing there is only 1 animal.AnimalTypeID (as most recent updated record) that needs to be queried against all breed.AnimalTypeID's

 

So the list of breeds that are displayed are the ones that only relate to the animal.AnimalTypeID (which is only 1)

 

Here is a query that will select the correct animal record:

 

mysql_select_db($database_woodside, $woodside);

$query_animal = "SELECT * FROM animal ORDER BY AnimalID DESC";

$animal = mysql_query($query_animal, $woodside) or die(mysql_error());

$row_animal = mysql_fetch_assoc($animal);

$totalRows_animal = mysql_num_rows($animal);

 

 

And here is the other query (so far) that selects the breedID WHERE breed.AnimalTypeID = animal.AnimalTypeID:

 

 

mysql_select_db($database_woodside, $woodside);

$query_Recordset1 = "SELECT BreedID FROM breed WHERE breed.AnimalTypeID = AnimalTypeID";

$Recordset1 = mysql_query($query_Recordset1, $woodside) or die(mysql_error());

$row_Recordset1 = mysql_fetch_assoc($Recordset1);

$totalRows_Recordset1 = mysql_num_rows($Recordset1);

 

 

 

Stay with me.....Please...

 

Thank You

 

[GingerRobot]

See your other topic. This is exactly why you should have added that topic as a reply to this post.