how can i pull this off in SQL?

Results 1 to 4 of 4

Thread: how can i pull this off in SQL?

  1. #1
    Pj Guest

    Default how can i pull this off in SQL?

    If I have a simple table that looks like this:<BR><BR>ID Color<BR>-- -----<BR>1 red<BR>2 white<BR>3 red<BR>4 blue<BR>5 red<BR><BR> can I pull all id&#039;s associated with red into a string without looping?<BR><BR>"SELECT ID FROM TABLE WHERE Color = &#039;red&#039;" requires that I loop through the recordset to build the string.<BR><BR>Any way to instantly create the string "1,3,5" with one query and no looping?<BR><BR>Thanks

  2. #2
    Join Date
    Dec 1969

    Default SQL? No. ASP/ADO/ASP? Easy!

    The very nature of SQL says that it *MUST* return all the records!<BR><BR>*BUT* if you look into ADODB.RecordSet.GetString you will discover that ADO provides a *very* easy way to do this.<BR><BR>Look here:<BR><BR>And here:<BR>&#060;%<BR>...<BR>Set RS = conn.Execute("SELECT Id FROM table WHERE Color=&#039;red&#039;")<BR>listOfIds = RS.GetString( , , "", "," )<BR>&#039; that leaves a trailing comma at the wipe it off:<BR>listOfIds = Left( listOfIds, Len(listOfIds) - 1 )<BR>...<BR>%&#062;<BR><BR>Presto! <BR><BR>If you are then going to turn around and convert that comma-delimited string into an array using SPLIT...well, don&#039;t do it. Instead read about ADODB.RecordSet.GetRows.<BR><BR>

  3. #3
    Shawn Phelps Guest

    Default RE: SQL? No. ASP/ADO/ASP? Easy!

    what if one of the ID&#039;s was 123, that would make it 1,2,3 wouldnt it?

  4. #4
    Join Date
    Dec 1969

    Default Answer to your question: No way.

    No. Never. Go read up on GetString via the link I gave you.<BR><BR>Did you just take my answer on faith instead of checking the reference material??? Shame on you. You *need* to read the case you want to do something just a little different than I case you might use it 6 months from now and I&#039;m not case...<BR><BR>Anyway...<BR><BR>The "row delimiter" (which I made a comma) is put *BETWEEN* rows...between records. If the field is "123", how in the world could a comma that is put BETWEEN ROWS be put into the middle of the field value?<BR><BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts