Page 1 of 1
searching a table
Posted: Mon Aug 23, 2004 1:24 am
by bugthefixer
i have a table which has 1 colum and may contain 100s of entries in that colum
if have 3 values then i want to search whether these three values exist in the only colum of that table
wat could be the query.
Posted: Mon Aug 23, 2004 3:01 am
by AngusL
"SELECT TableName.* FROM TableName WHERE(NameOfColumn = 'Entry1' OR NameOfColumn = 'Entry2' OR NameOfColumn = 'Entry3')" I think would work, if I understand you.
Posted: Mon Aug 23, 2004 3:29 am
by bugthefixer
well it gives me result if either of the three values exist. but wat i want is it should give result if all the three values exist in that table
Posted: Mon Aug 23, 2004 6:47 am
by AngusL
Oh right, that I believe is still fairly simple
"SELECT TableName.* FROM TableName WHERE(NameOfColumn = 'Entry1' AND NameOfColumn = 'Entry2' AND NameOfColumn = 'Entry3')".
EDIT: Wait, no that's stupid. It can't equal all of them at the same time. *Smacks head*
Perhaps cycle through the database, check to see if each row is one of the entries you are looking for, and set a variable to 1 once you find the first, 2 when you find the second, and 3 when you find the third. Then check if x = 3, and if it does, do whatever.
Equivelant ASP code (in case my description isn't good enough)
Code: Select all
Dim x=0 'Declare the variable that we'll use to count the number of pertinent entries we find
Dim i 'Declare the variable we'll use for the for loop. You could also use Loop Until.
For i = 1 to 100000000 'Loop 100 Million times.
strSQL="SELECT TableName.* FROM TableName" 'Setting the query string. We'll assume that we have included the file with the database setup stuff.
RecordSet.Open strSQL, ADODBConnection 'Open the recordset with the Querystring and the connection presetup.
If (RecordSet.Fields("ColumnName") = "Entry1") OR (RecordSet.Fields("ColumnName") = "Entry2") OR (RecordSet.Fields("ColumnName") = "Entry3") Then 'If the current row in the column 'ColumnName' has an entry of Entry1, Entry2 or Entry3 then
x = x + 1 'Set x to equal x's current value + 1. Forgotten the increment function. :P
End If 'End the if.
If (RecordSet.EOF) OR (x = 3) Then 'If that's the end of the RecordSet or X=3, indicating we have found all three entries then
Exit For 'Exit the for loop.
End if 'End the if.
RecordSet.MoveNext 'Move to the next row in the database.
Next 'Go back to the start of the For loop.
If x=3 Then 'If x=3, indicating we have indeed found all three entries, and we haven't exited the loop for another reason, then
'Do whatever
Else 'If x <> 3, indicating we haven't found all three entries, but have left the loop for another reason then
'Do whatever else
End if 'End the if.
Perhaps not the cleanest, or most useful, but it might give you a hint on how to do it in PHP.
Posted: Mon Aug 23, 2004 10:59 am
by feyd
untested
Code: Select all
SELECT a.* FROM foo a, foo b, foo c WHERE a.column = 2, b.column = 3, c.column = 10