Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Combining multiple queries of the same table into one SQL statement

Tags:

sql

mysql

I am trying to combine these MySQL queries but not getting them right. Here is the pseudocode that I am hoping 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

like image 435
user1115729 Avatar asked Dec 26 '11 01:12

user1115729


People also ask

Can multiple queries be created at one table?

To create a multi-table query: Select the Query Design command from the Create tab on the Ribbon. In the dialog box that appears, select each table you want to include in your query and click Add. You can press and hold the Ctrl key on your keyboard to select more than one table.


1 Answers

First let me say that mjfgates could be right. The original psuedo code is not "bad" just because it takes two steps. The more complex your SQL statement that greater chance the query engine may not find an optimal plan. In this particular case that's less likely because there's just a single table we're referencing multiple times, but it is something to keep in mind in general in these situations. Getting a SQL down to one statement is not always a worthy goal in itself.

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)
    )
like image 114
TetonSig Avatar answered Sep 30 '22 17:09

TetonSig