Have an account? Sign in
Login  Register  Facebook
Web Development Problems
request for help with select query
I need a help with selecting the rows in a table depending on the status in previous table.

Table1
Column1   Column2         Status
Smith       Alex             1
Smith       Mark             0
John        Smith            1
I have second table with 2 columns. I want to select only those rows which have ANY of the user of table1 in column1 or column2 with status 1. Table 2 
Column1        Column2     
Smith          Anderson
Martin         Walker
Alex            Scott

For example, Table1, the first row has status 1. Now i want to SELECT the rows from table2, which have "smith" OR "alex" in Column1 OR Column2 (Either Smith, or Alex). So, from Table2 it should select Row1 and Row3.

Do I have to join the tables? Wont that be slow?
Can I perform SELECT with EXISTS query?

Any help will be highly appreciated.
Started: September 18, 2011 Latest Activity: September 18, 2011 select mysql join
6 Answers
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)

Posted: MacOS
In: September 18, 2011

Thanks for the answer. 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
September 18, 2011

what do you mean by "select opposite column to that in table1" ?
September 18, 2011

Sorry if its not clear, 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".
September 18, 2011

Dear experts,
With the of your answers above, I have made this query, can you please suggest if it is valid ? (If Not, can you please 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)
               )

Posted: mac
In: September 18, 2011

Use:
SELECT t.column1,
       t.column2
  FROM TABLE_2 t
 WHERE EXISTS(SELECT NULL 
               FROM TABLE_1 a
              WHERE 'smith' IN (a.column1, a.column2)
                AND (   a.column1 IN (t.column1, t.column2)
                     OR a.column2 IN (t.column1, t.column2)))

Posted: is_set
In: September 18, 2011

Thanks a lot for this. This is what I was looking for. I need to do some change in it. Now, Instead of selecting 'smith', I want to select the opposite column against, how can i do that? For example, Column1 is Smith, i want to select the second column against it. And if the smith is in second column, i want to select the first column. Thanks again for the help. This website has been very helpful to me.
September 18, 2011

i think IN (a.column1, a.column2) will do this job too
September 18, 2011

I meant something like "SELECT IF (column1 = "smith", column2, column1), I think this statement will check if column1 has smith then it will select column2, otherwise it will select column1. Is it so? If yes, how can I use this in above? Thanks for help.
September 18, 2011

Also, it does not have the condition of STATUS = 1, where i can add that?
September 18, 2011

Thanks GO for the answer. It is very helpful, However I don't want to get ALL the rows where the status is 1. I want to check a specific user. For example, in the Table1, I want to look for only user 'smith'. For example if the Table1 has more records as below where other users also have status 1, even then it should not select last 2 rows because they don't have Smith in them.
Thanks for your help.

Column1   Column2         Status
Smith       Alex             1
Smith       Mark             0
John        Smith            1 
Brain       Julia            1
Colin       Craig            1

Posted: mac
In: September 18, 2011

PS. As I said i want to look for a specific user Smith, it can be either in Column1 or Column2.
September 18, 2011

You can use a subquery to select the names that you want to match and then join with that subquery:
SELECT SELECT T2.Column1, T2.Column2
FROM Table2 T2
JOIN
(
    SELECT Column1 AS Name FROM Table1 WHERE Status = 1
    UNION
    SELECT Column2 AS Name FROM Table1 WHERE Status = 1
) T1
ON T2.Column1 = T1.Name OR T2.Column2 = T1.Name
Result:
Column1  Column2
-------------------
Smith    Anderson
Alex     Scott
Here\'s a solution using only a JOIN, but I\'m assuming that you have a primary key on each table, called ID.
SELECT * FROM Table2 t2
INNER JOIN Table1 t1
ON t2.Status = 1 AND
  (t1.Column1 = t2.Column1 OR t1.Column1 = t2.Column2 OR
   t1.Column2 = t2.Column1 OR t1.Column2 = t2.Column2)
GROUP BY t2.ID
An INNER JOIN requires all conditions to be true in order for the row to be returned. The GROUP BY clause effectively removes the duplicate rows from Table2 that match multiple rows in Table1. Using EXISTS:
SELECT t.column1,
       t.column2
  FROM TABLE_2 t
 WHERE EXISTS(SELECT NULL 
                FROM TABLE_1 a
               WHERE a.status = 1
                 AND (   a.column1 IN (t.column1, t.column2)
                      OR a.column2 IN (t.column1, t.column2))

Posted: Go
In: September 18, 2011

try this
select a.column1, a.column2, a.status
	from Table1 a inner join Table2 b
	on (a.column1 = b.column1 or a.column2 = b.column1)
where a.status = 1

Posted: MacOS
In: September 18, 2011

Your Answer

xDo you want to answer this question? Please login or create an account to post your answer