Combining multiple queries from the same table into a single SQL statement

Question

I am trying to combine these MySQL queries, but I am not getting them correctly. Here is the pseudocode that I hope to combine to get a single-line sql statement.

$var1 = "abc" $var2 = "def" IF ( $var1 IN (SELECT DISTINCT col1 FROM t1) ) { SELECT colA, colB, colC FROM t1 WHERE col1 = $var1 AND col2 LIKE '%$var2%' } ELSE { SELECT colA, colB, ColC FROM t1 WHERE col2 LIKE %$var1% } 

thanks

Answer 1

First let me say that mjfgates may be correct. The original psuedo code is not "bad" just because it takes two steps. The more complex your SQL statement, the more likely it is that the query engine will not be able to find the optimal plan. In this particular case, this is less likely because there is only one table that we refer to several times, but this is something that needs to be remembered in general in these situations. Getting SQL to one statement is not always a worthy goal.

Now, to answer your question:

  select colA,colB,colc from table1 t1 where ( (col1 = $var1 and col2 like '%$var2%') and EXISTS (select 1 from table1 t2 where t2.col1 = $var1) ) or ( (col2 LIKE %$var1%) and NOT EXISTS (select 1 from table1 t3 where t3.col1 = $var1) ) 

Answer 2

I think ... I would not do that. This is what is stored in procedures and views, each of which has an if statement. Just start the selection in order from most defined to smallest and return the first result that gives the rows.

In addition, I can see an error here. What happens if the table has a column col1 = $ var1, but none of these elements have col2, like $ var2? What is going to happen?