decreasing the amount of database calls

[ulthane]

Hello guys, i got the following code:

sql1= "SELECT * FROM Animals WHERE (adoptionstatus= 'זמין')  AND (animaltype= 'חתול') ORDER BY FileID DESC"
RS.Open sql1, conn, 3, 3
If Not RS.EOF then
 Do While Not RS.EOF
  sName = RS("PetName")
  sDesc = RS("Desc")
  sDesc = RS(sDesc, vbNewLine, "<br/>")
  Response.Write "<tr>"
  ' Code that displays Pet's infos
  iPetID = RS("FileID")
  sql2 = "SELECT * FROM pictures WHERE FileID=" & iPetID
  RS2.Open sql2, conn, 3, 3
  Response.Write "<td width=260>"
  Do While Not RS2.EOF
   sImage = RS2("FileID")
   ' Code that displays Pets images
    RS2.MoveNext
  Loop
  Response.Write "</td>"
  RS2.close
  RS.MoveNext
 Response.Write "</tr>"
 Loop
RS.close

I want to know if its possible to change this code to a shorter code, using just a single recordset instead of 2, if yes can anybody show me a way how to do it?

Thanks,
Ulthane

[r937]

you should use a single join query

since you have used the dreaded, evil "select star", it is difficult to write the join query, but i hope you will see where to put the "additional infos" columns if you need them

SELECT a.FileID
     , a.PetName
     , a.Desc
     , a.OtherAnimalInfos
     , p.OtherPetInfos
  FROM Animals AS a
LEFT OUTER
  JOIN pictures AS p
    ON p.FileID = a.FileID
 WHERE a.adoptionstatus= '????'
   AND a.animaltype= '????'
ORDER 
    BY a.FileID DESC

[ulthane]

hmm, this is the first time i see that join query stuff, took a quick look at tizag tutorial and understood alittle bit on how it works..
Now, i put that in SQL1 (ofc with some changes according to what i need) and then i wont need SQL2 anymore?
can u show me please how the new code should look like after adding this sql query?

Thanks,
Ulthane

[r937]

can u show me please how the new code should look like after adding this sql query?

not me, sorry, no, i don't do whatever language that code is (vbscript? asp? i have no idea)

[ulthane]

oh, thats ASP well ill be trying to play around abit with it, if ill get into troubles ill post here.

Thanks for the help

[r937]

... if ill get into troubles ill post here.

i have a better idea -- post in the asp forum, not this one

[ulthane]

Hey again guys,
ill need help again on this matter, i've changed my tables so now it should be easier reducing the amount of SQL calls, atm i use it that way:

"SELECT FileID, PetName, Desc FROM animals WHERE adoptionstatus= 'Available' AND animaltype= 'cat' ORDER BY FileID DESC"
' Getting pet infos using a outer DO WHILE
"SELECT FileName FROM pictures WHERE UniqueNum=" & RS("FileID")
' Getting Pet Images using a inner DO WHILE

i must mention each pet in the animals table can have more than 1 pic on the Pictures table, while FileName is the path to the image, Now how can i get all the infos without having to do the inner SQL command?

[r937]

Now how can i get all the infos without having to do the inner SQL command?

see post #2 in this thread

[ulthane]

i've already tried it and it still requires me to do another do while to display the images, maybe u could show me otherwise.....because saying "look at post #2" doesn't help me much, im not yet blind.

[r937]

sorry, mate, i can only give you help with the sql, i don't do asp

please test the sql and make sure it's returning the right information, and then you can repost your question in the asp forum so that they can help you with the display

[ulthane]

im sure its more of a database related question rather than ASP question
i dont need help with displaying i just gave the code above to give u some info on how it is currently looking, so my goal is instead of using:

' Select from Animals
' Getting pet infos using a outer DO WHILE
' Select from pictures
' Getting Pet Images using a inner DO WHILE

it would look something like that :

' Select from animals AND pictures
' Something like this:
sql = "SELECT a.FileID, a.Desc, p.FileName FROM Animals AS a LEFT OUTER JOIN pictures AS p ON p.UniqueNum = a.FileID WHERE a.AnimalType= 'dogs'" 
' And now, do WHILE not EOF....
' Getting infos AND images

the problem i encounter with the code you gave me above is that it works only when there is 1 photo (p.UniqueNum field) for each pet, if there's more than 1 photo it will just display the first one it gets and ignore the rest

Well if there is no way of doing this without a 2nd SQL call then fine ill stay with 2, but if any1 got any idea of doing that with just 1 then i'd like to hear it

Thanks for the help

[r937]

the problem i encounter with the code you gave me above is that it works only when there is 1 photo (p.UniqueNum field) for each pet, if there's more than 1 photo it will just display the first one it gets and ignore the rest

that would be a problem with your asp logic

using a LEFT OUTER JOIN, the query returns 0, or 1, or many pictures for each animal, depending on how many pictures there are for each animal

your asp logic has to decide what to do when there is 0, or 1, or many

but another query is not needed

[awasson]

SELECT a.FileID
     , a.PetName
     , a.Desc
     , a.OtherAnimalInfos
     , p.OtherPetInfos
  FROM Animals AS a
LEFT OUTER
  JOIN pictures AS p
    ON p.FileID = a.FileID
 WHERE a.adoptionstatus= '????'
   AND a.animaltype= '????'
ORDER 
    BY a.FileID DESC

Ok, I've been following this over on the ASP side so I'll chime in here...

@ulthane: It's a bit difficult to figure out what's going on without seeing the structure of your images table. In the above SQL statement, what represents the actual file name for the picture? I only see one reference to the pictures table in the SQL statement (p.OtherPetInfos).

[ulthane]

SELECT a.FileID
     , a.PetName
     , p.FileName
  FROM Animals AS a
LEFT OUTER
  JOIN pictures AS p
    ON p.FileID = a.FileID
ORDER 
    BY a.FileID DESC

Lets just take the example SQL on the above, on the following tables: (btw yeh from the pictures table i only need the fileName where the pFileID = aFileID

Animals:
FileID / PetName
1 / Fido
2 / Rex

Pictures
FileID / FileName
1 / 1.jpg
1 / 2.jpg
2 / 3.jpg

What will be the results of the SQL? how will they be shown...

thanks for the help

[r937]

What will be the results of the SQL?

FileID PetName FileName
 1     Fido    2.jpg
 1     Fido    1.jpg
 2     Rex     3.jpg

[awasson]

That's what I was looking for (p.FileName). Great, we can take this back to the ASP forum and you should be able to loop through the results now.

Thanks Rudy.