Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-10-25 : 21:43:23
|
| Glenn writes "I was looking at http://www.sqlteam.com/item.asp?ItemID=289and i think this is what i need. But i want to want to know your opinion, i will also go try it out after this but would still ike feedback.I am doing a inner join on two tables.One table is address, the other is dr name.Each doctor can have several addresses, billing, main office, home etc and the Dr name of course only has one record for each doctor. These are linked by dr_id.My current statments is thisSQL = "SELECT DRNAME.dr_lname, DRNAME.dr_fname, DRNAME.spec1, DRNAME.spec2, DRNAME.spec3, DRNAME.spec4, DRNAME.org_name, DRNAME.sex, DRNAME.dr_id, ADDRESS.city, ADDRESS.sch_type FROM DRNAME INNER JOIN ADDRESS ON DRNAME.dr_id = ADDRESS.dr_id" ' put the list of searchable fields into an array, thus:formFields = Array("lastname", "specialty","practice","sex","city")' and the list of TABLE field names into a parallel array, thus:dbFields = Array("DRNAME.dr_lname", "DRNAME.spec1", "DRNAME.org_name", "DRNAME.sex", "ADDRESS.city") where = "" ' we will build the WHERE clause...delimiter = " WHERE "For fnum = 0 To UBound ( formFields ) fval = Request.Form ( formFields(fnum)) If ("X" & fval) <> "X" Then ' user gave us a value for this field! ' ...build one LIKE clause, for this field... ' (of course, this could be an = test instead of LIKE) where = where & delimiter & dbFields(fnum) & " LIKE '%" & fval & "%'" ' change delimiter to the conjunction now! delimiter = " AND " ' this might be " or/and " instead! End IfNext' the WHERE clause is built...or is it?If Len(where) = 0 Then ' ??? the user did not give *any* values ??? ' ??? YOU must decide what to do ???SQL = SQL End If' so build the full query:SQL = SQL & where & " AND (((ADDRESS.sch_type) = '3' OR (ADDRESS.sch_type) = '6')) ORDER BY org_name, dr_lname"This will return the dr name and other info each time there is an address of ADDRESS.sch_type 6 or 3 ( see the sql right above this paragraph.After reading it seems distinct will only show one!What do you think?thanks in advanceSQL 7, NT4, Glenn" |
|
|
|
|
|
|
|
distinct?