VB6 SQL string

[Clon]

Hi, Hans:

I'm still trying to understand your database structure and what you want to get. There's nothing wrong in the following SQL query. It is your query, just a little bit modified to eliminate all where clauses and convert them to joins.

SELECT C.PairNumberID, 
           C.RoundID, 
           C.Coupled, 
           C.DateFirstEgg, 
           C.TotalEggs, 
           C.DateHatch, 
           C.QtyHatched, 
           C.DateRinged, 
           C.DateFlyOut, 
           C.Young1, 
           C.Young2, 
           C.Young3, 
           C.Young4, 
           C.Young5, 
           C.Young6, 
           C.Young7, 
           C.Young8, 
           C.Young9, 
           C.Young10, 
           P.CageNumber, 
           P.RingSize, 
           M.BirdID, 
           M.BirdName, 
           M.BirdColor, 
           M.BirdOrigin, 
           F.BirdID, 
           F.BirdName, 
           F.BirdColor, 
           F.BirdOrigin, 
           S.Species
FROM Species AS S 
  INNER JOIN (Birds AS M 
  INNER JOIN ((BirdPairing AS P 
  INNER JOIN Birds AS F 
     ON P.FemaleID = F.BirdID) 
  INNER JOIN BreedingCard AS C 
     ON P.PairNumberID = C.PairNumberID) 
     ON M.BirdID = P.MaleId) 
     ON S.SpeciesID = P.SpeciesID

The problem here is exactly what information do you intend to retrieve?

In this one everything works except for (BirdPairing.CageNumber, BirdPairing.RingSize) and the Male and Female inner joins they stay the same.
They have to change.

The male and female tables give you information from the Birds table with the IDs contained in the BirdPairing table, which seems correct for me.

I have simulated your database, but I'm not sure what the primary keys should be. Please, check your database structure to see that it meets 3th. Normal Form.
Particularly:
- Is the species a characteristic of the bird pairing, or is it a characteristic of the bird?
- Each table should have a primary key ---> What is the primary key of BreedingCard? What does it represent?
- You shouldn't have 10 Young fields in the BreedingCard table. Instead, you should create another table Youngs which relates the primary key of the BreedingCard table with the primary key of births
- There is a "RingSize" field in the BirdPairing table, and a "DateRinged" in the BreedingCard table, while it seems they refer to the same thing.

What does the field RoundID in the BreedingCard table represent?

This is what I understand of your old design from your query:



You could simplify your database structure like this. Then you should make two separate queries, one to retrieve data about the couple, a second one to retrieve all the youngs data. Give it a thought: