simple query need to return male&female records

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

simple query need to return male&female records

Author

Topic

asp_rin
Starting Member

4 Posts

Posted - 2004-09-08 : 21:51:39

here is the code for the query I have it returns male when selected or female if selected but if both are selected of course it returns 0 due to each record has either male or female NOT male AND female..

i hope it makes since== same thing with the code in blue I need it to return all records that have either 0, 1 or 2 data

If Shortname <> "" Then
whereclause = whereclause & "Results_Public.Shortname LIKE '" & Shortname & "%' AND "
End If
If Animal_Identifier <> "" Then
whereclause = whereclause & "Results_Public.Animal_Identifier LIKE '" & Animal_Identifier & "%' AND "
End If
If Breed <> "" Then
whereclause = whereclause & "Results_Public.Breed LIKE '" & Breed & "%' AND "
End If
If RegNumber <> "" Then
whereclause = whereclause & "Results_Public.RegNumber LIKE '" & RegNumber & "%' AND "
End If
If RegName <> "" Then
whereclause = whereclause & "Results_Public.RegName LIKE '" & RegName & "%' AND "
End If
If Gender1 <> "" Then
whereclause = whereclause & "results_public.Gender = " & Gender1 & " AND " 'when selected return & also return gender2 if gender2 is also selected
End If
If Gender2 <> "" Then
whereclause = whereclause & "results_public.Gender = " & Gender2 & " AND "
End If
If ResultMarbling0 <> "" Then
whereclause = whereclause & "results_public.ResultMarbling = " & ResultMarbling0 & " AND " 'same here return this
End If
If ResultMarbling1 <> "" Then
whereclause = whereclause & "results_public.ResultMarbling = " & ResultMarbling1 & " AND " 'this
End If
If ResultMarbling2 <> "" Then
whereclause = whereclause & "results_public.ResultMarbling = " & ResultMarbling2 & " AND " '& thisEnd If
If ResultTenderness0 <> "" Then
whereclause = whereclause & "results_public.ResultTenderness = " & ResultTenderness0 & " AND " 'another set here
End If
If ResultTenderness1 <> "" Then
whereclause = whereclause & "results_public.ResultTenderness = " & ResultTenderness1 & " AND " 'here
End If
If ResultTenderness2 <> "" Then
whereclause = whereclause & "results_public.ResultTenderness = " & ResultTenderness2 & " AND " 'here
End If
If ResultTenderness20 <> "" Then
whereclause = whereclause & "results_public.ResultTenderness2 = " & ResultTenderness20 & " AND " 'another set
End If
If ResultTenderness21 <> "" Then
whereclause = whereclause & "results_public.ResultTenderness2 = " & ResultTenderness21 & " AND " 'set
End If
If ResultTenderness22 <> "" Then
whereclause = whereclause & "results_public.ResultTenderness2 = " & ResultTenderness22 & " AND " 'set
End If
whereclause = whereclause & "results_public.ResultMarblingPublish = " & -1 & " AND "
whereclause = whereclause & "results_public.ResultTendernessPublish = " & -1 & " AND "
whereclause = whereclause & "results_public.ResultTenderness2Publish = " & -1 & " AND "
If Right(whereclause, 4) = "AND " Then
whereclause = Left(whereclause, Len(whereclause) - 4) ' strip off 'AND '
Else
whereclause = Left(whereclause, Len(whereclause) - 6) ' strip off 'WHERE '
End If
query = "SELECT * FROM Results_Public " & whereclause
Session("SSS_query") = query
else
query = Session("SSS_query")
end if

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-09-08 : 23:35:10

I've had to make some assumptions here because you haven't really told us much:

If Gender1 <> "" AND Gender2 <> "" Then
    whereclause = whereclause "results_public.Gender IN (" & Gender1 & ", " & Gender2 & ") AND"
Else
    If Gender1 <> "" Then
        whereclause = whereclause & "results_public.Gender = " & Gender1 & " AND " 
    End If
    If Gender2 <> "" Then
        whereclause = whereclause & "results_public.Gender = " & Gender2 & " AND " 
    End If
End If

tip: You'd find things easier if you use table aliases (see BOL for more info).

asp_rin
Starting Member

4 Posts

Posted - 2004-09-09 : 01:21:23

thanks you guys rock.. got me on the right track I believe..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources