need help in building an sql query

Results 1 to 2 of 2

Thread: need help in building an sql query

  1. #1
    Join Date
    Dec 1969

    Default need help in building an sql query

    Hi all,<BR> I urgently need help on building an sql query for my advanced search.<BR>I have a view which consists of fields<BR>for ex <BR>1. propID -- int<BR>2. propName - varchar<BR>3. proptype - varchar<BR>4. noofbedrooms - tinyint<BR>5. Terrace - bit<BR>6. cargarage - bit<BR>7. swimmingpool - bit<BR>8. garden -bit<BR>9. golf -bit<BR>etc.,etc.,<BR>What i need now is i have to display the records <BR>in such a way that the selected features will be presented first. <BR>for ex. in the search they selected swimmingpool,golf and my results page should first display records which is having these three features and then with two features and then with one feature and at the end which are not having this features but whcih satisfies other criteria like noobedrooms etc.,<BR>What i mean to say is the features selection is a optional selection.<BR>You can go to this site, they have something similar in there site..<BR><BR><BR>Thanks in advance for your help..<BR>regards,<BR>vani.<BR><BR><BR>

  2. #2
    Join Date
    Dec 1969

    Default try this

    I think you could try something like this:-<BR><BR>SQL = "Select *,"<BR>SQLN = ""<BR>if form.swimmingpool = true then<BR> SQLN = SQLN & " instr([swimmingpool]," & form.Swimmingpool & ")"<BR>end if<BR>if form.cargarage = true then<BR> SQLN = SQLN & "+instr([cargarage]," & form.Cargarage & ")"<BR>end if<BR>etc etc<BR>SQL = SQL & SQLN & " as NoMatches from TABLE order by " & SQLN<BR><BR>This should return all records starting with thos that match most closely to the request plus a field named NoMatches that should be total of matches as instr will return 1 if it matches and 0 if it doesnt. The SQL statements (SQLN) may need refining depending on the datatypes.<BR><BR>I hope this helps<BR>Regards

Posting Permissions

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