How to determine if a MySQL query is valid?

Question

With the answers above, I have made this query, is it valid? If not, how can I correct it?

SELECT *,
 FROM TABLE_2 t
 WHERE EXISTS(SELECT IF(column1 = 'smith', column2, column1)       
 FROM TABLE_1 a
 WHERE 'smith' IN (a.column1, a.column2)
        AND a.status = 1              
                AND ( 'smith' IN (t.column1, t.column2)
               )

The below answer makes it seem this question was rather too broad even when it was asked, so it makes sense for it now to be put on hold. — halfer
– halfer, Commented Aug 23, 2017 at 18:05

MJB · Accepted Answer · 2010-06-21 16:03:57Z

1

To start with, the comma after select * does not belong.

Second, you alias your tables (table_2 t and table_1 a), but then you don't consistently use the aliases, so you might have issues at run time. Also from a maintenance perspective, I think most folks prefer to use aliases when declared, and no aliases otherwise.

Third, you do a comparison against cols from the t table in the outer select ('smith' in (t.column1, t.column2) ), when that appears unnecessary. You can just do it in the outer select. In other words, you can move that terminal paren to before the AND ('smith'...

As for whether it works -- I have no idea, since I don't know what you are trying to accomplish.

Combined, that would leave you with :

SELECT t.*
FROM TABLE_2 t
WHERE EXISTS (SELECT IF(a.column1 = 'smith', a.column2, a.column1)       
              FROM TABLE_1 a
              WHERE 'smith' IN (a.column1, a.column2)
              AND a.status = 1)
AND ( 'smith' IN (t.column1, t.column2)

answered Jun 21, 2010 at 16:03

MJB

7,6862 gold badges34 silver badges41 bronze badges

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

3 Comments

Mostafa Elkady Over a year ago

Im confused about SELECT IF(a.column1 = 'smith', a.column2, a.column1). What Im actually trying to do is that, if there is a row, in column1 or column2 having 'smith', then dont select that column, instead select opposite column to that in table1. If you see the first post there you can see table structure and my question again. Hope you can help me sorting out this problem. Thanks - kaash1 1 minutes ago

MJB Over a year ago

@moustafa: what do you mean by "select opposite column to that in table1" ?

Mostafa Elkady Over a year ago

please have a look at Table 1 and Table 2 in my original post. I want to build a query like: "I want to select all rows from Table 1 where there is smith in column1 or column2. If there is smith in column 1, then select the value from column 2, and if there is smith in column2, then select value in column 1 of the row, then select all the rows from Table 2 which contains that value in column1 or column2 of table 2, which we got by selecting from Table 1"

Collectives™ on Stack Overflow

How to determine if a MySQL query is valid?

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related