How to write delete query with inner join?

Nested loop/if...then TABLE display problem (ASP)

• I am returning a recordset with the following query: *************** [CODE] ******************** SELECT sdl.supplier_division_id, s.supplier_id, s.supplier_name, sdl.status_id, sl.status_desc FROM supplier AS s INNER JOIN supplier_division_lu AS sdl ON sdl.supplier_id = s.supplier_id INNER JOIN division_lu AS d ON d.division_id = sdl.division_id INNER JOIN status_lu AS sl ON sl.status_id = sdl.status_id ORDER BY s.supplier_name *************** [/CODE] ****************** The query returns the correct information. However, I am having a difficult time displaying the information. Currently, I am using the following ASP/HTML: *************** [CODE] ******************** Set rsGetList = objRec rsGetList.MoveFirst ...some html code with <table> and <th> Do While NOT rsGetList.EOF Response.Write ("<TD>") Response.Write rsGetList("supplier_name") IF varSupplier_ID = rsGetList("supplier_id") THEN Response.Write ("<TD>") Response.Write ("<INPUT type = checkvox id=checkbox1 name=") Response.Write ("'" & rsGetList("division_name") & "'") Response.Write (rsGetList("status_desc")) Response.Write (">") Response.Write rsGetList("division_name") Response.Write ("<BR>") Response.Write ("</TD>") END IF Response.Write ("<TD>") ...html buttons Response.Write ("</TR">) rsGetList.MoveNext Loop *************** [/CODE] ******************* The table diplays the following: SUPPLIER A | DIVISION 1 | BUTTONS ------------------------------------------- SUPPLIER A | DIVISION 2 | BUTTONS ------------------------------------------- SUPPLIER A | DIVISION 3 | BUTTONS ------------------------------------------- SUPPLIER B | DIVISION 1 | BUTTONS ------------------------------------------- SUPPLIER B | DIVISION 2 | BUTTONS I would like the table to display as follows: | DIVISION 1 | SUPPLIER A | DIVISION 2 | BUTTONS | DIVISION 3 | ------------------------------------------- SUPPLIER B | DIVISION 1 | BUTTONS | DIVISION 2 | The example directly above should have only two rows as opposed to five rows in the other table display example. Please advise. Kind regards, Krickles

• Answer:

  Greetings Krickles! I am posting a code snippet below. Make a backup of your original asp before you do anything else. Try these changes in a COPY of your page so that you can always roll back to your version if you run into any problems. Okay, with that said, in the copy of your page, delete all of the text from the characters "... some html code with" all the way through the word "loop" on the bottom and replace it with the code below. There are comments included that should answer most of your questions, but please let me know if you need clarification on any fine points. This should create the table structure you are looking for. Without your complete code to look at, there may need to be a few tweaks. Again, let me know if I can clarify anything. Happy coding! Sincerely, Thinkout-ga ******************** [CODE] ******************** Dim lSupplierID, lLastSupplierID 'Start with mis-matched values (Fires the first 'if' event) 'Assumes your valid results set does NOT have an id of -1 lLastSupplierID = -1 lSupplierID = CDbl(rsGetList("supplier_id")) Do While NOT rsGetList.EOF If lLastSupplierID <> lSupplierID Then 'We're assuming the <tr> has already been opened above... 'Write the supplier cell Response.Write ("<TD>") Response.Write rsGetList("supplier_name") Response.Write("</td>" & vbCrLf) 'Open the cell for the division names Response.Write ("<TD>") End If 'Write out the division names and check boxes 'check box, name and id should match Response.Write ("<INPUT type=checkbox id=") Response.Write ("'" & rsGetList("division_name") & "' name=") Response.Write ("'" & rsGetList("division_name") & "' ") 'Does status_desc return 'Checked'? Response.Write (rsGetList("status_desc")) Response.Write (">") 'Write the name and the break for the next Response.Write rsGetList("division_name") Response.Write ("<BR>") 'Iterate the control variable lLastSupplierID = lSupplierID 'Get the next record rsGetList.MoveNext If NOT rsGetList.EOF Then 'Get the next row's supplier ID lSupplierID = CDbl(rsGetList("supplier_id")) 'Check if we have changed Suppliers If lLastSupplierID <> lSupplierID Then 'Close the cell Response.Write ("</TD>" & vbCrLf) 'Open the buttons cell Response.Write ("<TD>") '...html buttons 'Close the buttons cell Response.Write("</td>" & vbCrLf) 'Close the row Response.Write ("</TR>" & vbCrLf) 'This is not eof, so open a new row Response.Write ("<TR>" & vbCrLf) End If Else 'Close the cell Response.Write ("</TD>" & vbCrLf) 'Open the buttons cell Response.Write ("<TD>") '...html buttons 'Close the buttons cell Response.Write("</td>" & vbCrLf) 'Close the row Response.Write ("</TR>" & vbCrLf) End If Loop ******************** [CODE] ********************