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?
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
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?
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?
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.
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
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
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).
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
well ill be trying to play around abit with it, if ill get into troubles ill post here.