CODEWITHSUNDEEP
sqlsql-server-2008
D.Trump123
D.Trump123

Reputation: 37

Select from multiple table columns with different values

I'm trying to create a search button that can filter through multiple table columns within a table, I have three seperate codes that can search a data on a particular table column (LastName, FirstName and DriverID):

SqlConnection Con = new SqlConnection();
Con.ConnectionString = "Data Source=PC-PC\\MIKO;
                        Initial Catalog=Caproj;
                        Integrated Security=True;";
Con.Open();
string qry = "select LastName from [Driver Table] where LastName=@id";
SqlCommand cmd = new SqlCommand(qry, Con);
cmd.Parameters.AddWithValue("@id", slastname.Text);
cmd.ExecuteNonQuery();
SqlDataAdapter da2 = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da2.Fill(dt);
if (dt.Rows.Count > 0)
 {
  MessageBox.Show("Valid Input");
 }
else
{
 MessageBox.Show("Invalid Input");
}

Is there a way where I can search them all at the same time without the need for making a separate filter for each? Something along the lines of this:

SELECT LastName, FirstName, DriverID 
FROM [Driver Table] 
WHERE LastName=@id 
  AND FirstName=@id 
  AND DriverID=@id

Upvotes: 0

Views: 37

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

If I understand correctly, don't you just want or:

SELECT LastName, FirstName, DriverID 
FROM [Driver Table] 
WHERE LastName = @id OR
      FirstName = @id OR
      DriverID = @id;

Or, more simply:

SELECT LastName, FirstName, DriverID 
FROM [Driver Table] 
WHERE @id IN (LastName, FirstName, DriverID);

Upvotes: 0

Prince
Prince

Reputation: 11

I am not sure if this what you need. But this query will return a 1 value when the id is existing from the 3 columns.

SELECT  
    CASE WHEN EXISTS (SELECT * FROM [Driver Table] WHERE LastName = @id) 
            THEN 1 
         WHEN EXISTS (SELECT * FROM [Driver Table] WHERE FirstName = @id) 
            THEN 1 
         WHEN EXISTS (SELECT * FROM [Driver Table] WHERE DriverID = @id) 
            THEN 1 
         ELSE 0 
    END

Upvotes: 1

Related Questions