VB6 SQL string

[hanssnah]

I don't know how to describe the problem
I have a sql string that is working

CardSQL = "SELECT RoundID, BreedingCard.PairNumberID, Coupled, DateFirstEgg, TotalEggs, DateHatch, QtyHatched, " _
     & "DateRinged, DateFlyOut, CageNumber, RingSize, " _
     & "Male.BirdID, Male.BirdName, Male.BirdColor, Male.BirdOrigin, " _
     & "Female.BirdID, Female.BirdName, Female.BirdColor, Female.BirdOrigin, Species " _
     & "FROM Birds AS Male, Birds As Female, Birds AS Young1, Birds AS Young2, " _
     & "BirdPairing, Species, BreedingCard, Misc " _
     & "WHERE BirdPairing.MaleID = Male.BirdID And BirdPairing.FemaleID = Female.BirdID And " _
     & "BirdPairing.SpeciesID = Species.SpeciesID And BreedingCard.PairNumberID = BirdPairing.PairNumberID"

I also needed fields( Young1, Young2, Young3, Young4, Young5, Young6, Young7, Young8, Young9, Young10 from table breedingcard)
I've put them in the string
so far so good still working

CardSQL = "SELECT RoundID, BreedingCard.PairNumberID, Coupled, DateFirstEgg, TotalEggs, DateHatch, QtyHatched, " _
     & "DateRinged, DateFlyOut, CageNumber, RingSize, " _
     & "Young1, Young2, Young3, Young4, Young5, Young6, Young7, Young8, Young9, Young10, " _
     & "Male.BirdID, Male.BirdName, Male.BirdColor, Male.BirdOrigin, " _
     & "Female.BirdID, Female.BirdName, Female.BirdColor, Female.BirdOrigin, Species " _
     & "FROM Birds AS Male, Birds As Female, Birds AS Young1, Birds AS Young2, " _
     & "BirdPairing, Species, BreedingCard, Misc " _
     & "WHERE BirdPairing.MaleID = Male.BirdID And BirdPairing.FemaleID = Female.BirdID And " _
     & "BirdPairing.SpeciesID = Species.SpeciesID And BreedingCard.PairNumberID = BirdPairing.PairNumberID"

Then I tried to do this that is working except when Young1 in the table is empty ore 0 then it won't work
( & "Young1.BirdID, Young1.BirdName, Young1.BirdColor, Gender, " _
& "Young2.BirdID, Young2.BirdName, Young2.BirdColor, Gender, " _)

when I leave the last part of ( & "BreedingCard.Young1 = Young1.BirdID And Young1.GenderID = Misc.MiscID AND " _
& "BreedingCard.Young2 = Young2.BirdID And Young2.GenderID = Misc.MiscID")
it works again but the result is wrong

CardSQL = "SELECT RoundID, BreedingCard.PairNumberID, Coupled, DateFirstEgg, TotalEggs, DateHatch, QtyHatched, " _
     & "DateRinged, DateFlyOut, CageNumber, RingSize, " _
     & "Young1, Young2, Young3, Young4, Young5, Young6, Young7, Young8, Young9, Young10, " _
     & "Young1.BirdID, Young1.BirdName, Young1.BirdColor, Gender, " _
     & "Young2.BirdID, Young2.BirdName, Young2.BirdColor, Gender, " _
     & "Male.BirdID, Male.BirdName, Male.BirdColor, Male.BirdOrigin, " _
     & "Female.BirdID, Female.BirdName, Female.BirdColor, Female.BirdOrigin, Species " _
     & "FROM Birds AS Male, Birds As Female, Birds AS Young1, Birds AS Young2, " _
     & "BirdPairing, Species, BreedingCard, Misc " _
     & "WHERE BirdPairing.MaleID = Male.BirdID And BirdPairing.FemaleID = Female.BirdID And " _
     & "BirdPairing.SpeciesID = Species.SpeciesID And BreedingCard.PairNumberID = BirdPairing.PairNumberID And " _
     & "BreedingCard.Young1 = Young1.BirdID And Young1.GenderID = Misc.MiscID AND " _
     & "BreedingCard.Young2 = Young2.BirdID And Young2.GenderID = Misc.MiscID"

I hope this is enough info else let me know

[Clon]

Hi, hanssnah!

There are several design issues I want to comment, but first, could you qualify all the fields with the name of the table, in order to better understand the structure of your tables?

i.e.: tablename.RoundID instead of just RoundID

Also, it would help if you show in which way the result of the last query is wrong.

Without that, all I can do is guess

Regards,

[hanssnah]

Hi Clon

Thanks so far I will change the names as you said and than i'll post the string asap.

It says Item not found in collection referring to Young1.Birdname

Regards

[hanssnah]

Hi Clon,

I don't know how to explain the problem properly sorry for that I'm a beginner with SQL string's.

I did qualify all the fields with the name of the table and found out the whole string isn't doing what I want.
So I started over with the string.
Maybe you can see more in this one.

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.
what I want to achieve is that the outcome is that I browse the table BreedingCard and that the fields BirdPairing.CageNumber, BirdPairing.RingSize
Based on BreedingCard.PairnumberID wil give the corresponding Records In BirdPairing.
Male.* and Female.* are values in Birdpairing corresponding to records in table Birds.
I also have to do that with the value in Young1 to Young10 that value has to look in Table Birds .

CardSQL = "SELECT BreedingCard.PairNumberID, BreedingCard.RoundID, BreedingCard.Coupled, " _
     & "BreedingCard.DateFirstEgg, BreedingCard.TotalEggs, BreedingCard.DateHatch, BreedingCard.QtyHatched, " _
     & "BreedingCard.DateRinged, BreedingCard.DateFlyOut, " _
     & "BreedingCard.Young1, BreedingCard.Young2, BreedingCard.Young3, BreedingCard.Young4, BreedingCard.Young5, " _
     & "BreedingCard.Young6, BreedingCard.Young7, BreedingCard.Young8, BreedingCard.Young9, BreedingCard.Young10, " _
     & "BirdPairing.CageNumber, BirdPairing.RingSize, " _
     & "Male.BirdID, Male.BirdName, Male.BirdColor, Male.BirdOrigin, " _
     & "Female.BirdID, Female.BirdName, Female.BirdColor, Female.BirdOrigin, Species " _
     & "FROM BreedingCard, ((BirdPairing " _
     & "inner join Birds AS Male on Male.BirdID = BirdPairing.MaleID) " _
     & "inner join Birds As Female on Female.BirdID = BirdPairing.FemaleID) " _
     & "inner join Species on BirdPairing.SpeciesID = Species.SpeciesID " _
     & "WHERE BreedingCard.PairNumberID = BirdPairing.PairNumberID"

Regards Hans

[Shaggy Hiker]

It looks like you are getting closer. I'd get rid of the use of *, which is just a wildcard for "all fields". Even if your really want ALL fields, it would be a bit more efficient to spell them out, as ugly as that might be. However, if you don't need ALL columns, then it would be more efficient to get just the ones you are interested in.

Other than that, it seems like you will be needing to join in the YoungX fields just like you did with Male and Female. That's going to make for a fair number of join statements, but such is life.

[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:

[hanssnah]

Hi Clon,

Thanks so far.
I'm pretty new to databases and sql
here I explain my database.

Table Birds : BirdID, RingNumber, SpeciesID, BirdName, BirdColor, BirdOrigin, GenderID

Table Misc : MiscID, Gender

Table Species : SpeciesID, Species

Table BirdPairing : PairNumberID, SpeciesID, CageNumber, RingSize, MaleID, FemaleID

Table BreedingCard : PairNumberID, RoundID, Coupled, DateFirstEgg, TotalEggs, DateHatch, QtyHatched, DateRinged, DateFlyOut, Young1, Young2, Young3, Young4, Young5, Young6, Young7, Young8, Young9, Young10


Relationsships:

Table Birds : BirdID to Table BirdPairing : MaleID, FemaleID
Table Birds : SpeciesID to Table Species : SpeciesID
Table Birds : GenderID to Table Misc : MiscID
Table BirdPairing : PairNumberID to Table BreedingCard : PairNumberID

The bolts are the primary keys
the species a characteristic of the bird pairing and the bird
the primary key of BreedingCard PairNumberID, RoundID it represent a combination of the 2 so a birdpair can have multiple breeding rounds
(In the database design I selected them both and than made them primarykey, and allowed duplications)

maybe this will help you to understand my structure and I want to do.

regards Hans