yandoo Posted January 24, 2008 Share Posted January 24, 2008 Hi there was hoping for a little help, I have 2 tables at the moment: Animal Type AnimalTypeID AnimalTypeName Breed BreedID AnimalTypeID I need a query that willl compare both tables and display only the BreedIDs of the records that have the same AnimalTypeID's as the AnimalType table. I started trying to write some kind of join but its not working....here what i got.... "SELECT breed.BreedID, AnimalType.AnimalTypeID ". "FROM Breed,AnimalType ". "WHERE Breed.AnimalTypeID = AnimalType.AnimalTypeID"; I think im doing something wrong here.....Please help me Thank You Quote Link to comment Share on other sites More sharing options...
teng84 Posted January 24, 2008 Share Posted January 24, 2008 and what wrong with that code i think thats correct! Quote Link to comment Share on other sites More sharing options...
yandoo Posted January 24, 2008 Author Share Posted January 24, 2008 Hi, thanks for reply! When i first tried it, it only displayed the AnimalTypeID from both tables. I want it to display only the BreedID's that are related to by the AnimalTypeID's of both tables. Any i deas, im really stuck here. Thank You Quote Link to comment Share on other sites More sharing options...
teng84 Posted January 24, 2008 Share Posted January 24, 2008 SELECT BreedID, Breed.AnimalTypeID FROM Breed INNER JOIN AnimalType ON AnimalType.AnimalTypeID = Breed.AnimalTypeID THIS? Quote Link to comment Share on other sites More sharing options...
cooldude832 Posted January 24, 2008 Share Posted January 24, 2008 need to group by something 90% of the time a two table query requires some sort of group by so try "SELECT breed.BreedID, AnimalType.AnimalTypeID ". "FROM Breed,AnimalType ". "WHERE Breed.AnimalTypeID = AnimalType.AnimalTypeID GROUP BY breed.BreedID"; or AniamlType.TypeID which ever is better for u Quote Link to comment Share on other sites More sharing options...
yandoo Posted January 24, 2008 Author Share Posted January 24, 2008 Hey thats brill! Your code works a charm! Thank you! Probably a bit of a silly question but is there away i can make it so ONLY BreedID is displayed??? Currently is diplays both BreedID & AnimalTypeID (correctly queried form both tables) (im looking to have the Breed results in drop down menu) Thank You Quote Link to comment Share on other sites More sharing options...
yandoo Posted January 24, 2008 Author Share Posted January 24, 2008 Sorry ignore mwe i was being stupid...just removed appropriate bit... thanks again every one for your help!! Tom Quote Link to comment Share on other sites More sharing options...
cooldude832 Posted January 24, 2008 Share Posted January 24, 2008 displaying is different than data being queried. Sometimes you query fields like a status field just to set another presentation area. I know you know what you mean, but just keep that clear a query of the SELECT nature "selects" certain data usually fields or statistical field data like COUNT MAX MIN AVG UNIQUE etc. This data drawn does not have to be displayed, it could be SELECTED just for the sake of being able to select it. Quote Link to comment Share on other sites More sharing options...
yandoo Posted January 25, 2008 Author Share Posted January 25, 2008 Hi there, I just realised ive been a plonker and its doesnt have the results i excpeted...... There are actually 3 tables that i think are needed to compare to get the desired result: Animal Type AnimalTypeID AnimalTypeName Breed BreedID AnimalTypeID Animal AnimalID AnimalName AnimalTypeID Basically i want to get drop down menu list to display the breeds of an animal. The breed is based on the AnimalType and the AnimalType depends on the Animal itself.... At this point on the page the user will be looking at an "edit animal record" page. So i ONLY need the breeds that are related to the AnimalTypeID of the Animal record that is being edited....Mindblowing !! I can get the AnimalID of the animal table because the update page simply queries the database for the latest updated record...But Once i know what AnimalID (and thus AnimalTypeID) it is, how do i compare it again...? Please help! Thank You Quote Link to comment Share on other sites More sharing options...
cooldude832 Posted January 25, 2008 Share Posted January 25, 2008 so you want to have successive select list based on the criteria of begining like Select Box A Species they select species type 2 Select Box B animals Displays only animals of species type 2 Select Box C Displays only breeds of Species 2 and animal type selected in B? Quote Link to comment Share on other sites More sharing options...
yandoo Posted January 25, 2008 Author Share Posted January 25, 2008 Hi, thanks for your help! Yeah thats the main idea its bascially a form that inserts/updates animal records in a database. But due to the nature of the data i hav had to do it over 2 stages. Stage one form is submits (details such as AnimalID, AnimalAge, Sex etc..INCLUDING AnimalTypeID) Stage two form displays just previously entered record BUT also adds more details on the form (Such as BreedID, which is then further updated). But i only want the BreedID of animal that relates to the AnimalType it is. AND the AnimalType will depend on the AnimalTypeID that is entered on stage 1 of the Animal form. At this point should point out that on stage 2 i have a query that displays the most recent record that has been insterted in stage 1. So how do i make this work?????? Thank You Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.