Combining multiple queries of the same table into one SQL statement

Question

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

I have tried SELECT IF('$var1' IN (SELECT DISTINCT col1 FROM t1), (colA, colB, colC), ) FROM t1 but I get lost in the flow for the if statements — user1115729
– user1115729, Commented Dec 26, 2011 at 1:24

Community · Accepted Answer · 2017-05-23 12:20:13Z

5

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

edited May 23, 2017 at 12:20

CommunityBot

11 silver badge

answered Dec 26, 2011 at 1:26

TetonSig

2,22716 silver badges21 bronze badges

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

Comments

mjfgates · Accepted Answer · 2011-12-26 01:39:35Z

3

I think... I wouldn't do it. This is just the sort of thing that stored procedures and views, each of which DO have the if statement, were made for. Just run the selects in order from most specific to least, and return the first result that gives rows.

Also, I might see a bug here. What happens if there are items in the table where col1 = $var1, but none of those items have col2 like $var2 ? What is supposed to happen?

answered Dec 26, 2011 at 1:39

mjfgates

3,4611 gold badge21 silver badges15 bronze badges

Collectives™ on Stack Overflow

Combining multiple queries of the same table into one SQL statement

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related