Select from multiple table columns with different values

Question

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

marc_s · Accepted Answer · 2018-03-25 20:13:22Z

1

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

edited Mar 25, 2018 at 20:13

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Mar 25, 2018 at 18:02

Prince

113 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

D.Trump123 Over a year ago

Do I just remove the "1", If I only want it to select the statement without returning a 1 value?

Prince Over a year ago

If that's the case just replace the AND to an OR on your query.

Gordon Linoff · Accepted Answer · 2018-03-25 21:28:52Z

0

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);

answered Mar 25, 2018 at 21:28

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

Select from multiple table columns with different values

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related